Use a Dynamic Range with Dates as a Criteria in FILTER Function

[LuisPulido]

So I manage to create a dynamic range of dates with all the 1st of month between a period of time.

What I want to do, is sum all the expenses of respective month from another table. That result should be another dynamic range of the same size as the one with the dates in order to create the following plot

The previous plot is made with a SUMIFS and works great, however the Sum need to be filter by some values from another table and as SUMIFS do not support to use formulas in the criteria range I need to use FILTER inside of a SUM and the I can multiply the result by SEQUENCE(X,,,0) to have the same size

The formula that I am using is:

=SEQUENCE(ROWS(X2#))*
SUM(FILTER(Table1[Sales],
(Table1[Date]>=X2#)*
(Table1[Date]<=EOMONTH(--X2#,0))
))

 

[rondeondo]

Have you tried the SUBTOTAL() function, this allows for sum or count or average etc but only shows the total for the visible cells, SUM does not vary with filtering. Also a SUBTOTAL() value will not be included in another SUBTOTAL, so you can put a number of SUBTOTALs in a column without effecting the value at the bottom.

 

[LuisPulido]

Have you tried the SUBTOTAL() function, this allows for sum or count or average etc but only shows the total for the visible cells, SUM does not vary with filtering. Also a SUBTOTAL() value will not be included in another SUBTOTAL, so you can put a number of SUBTOTALs in a column without effecting the value at the bottom.

I don't explain my self properly. The filters are not for the table, the filters will filter the formulas. What I am making is a dashboard, so the user will not access to the tables. The user will only use some activeX forms to apply filters to the calculations. Power Bi and Power Pivot are not an option Neither.

 

[Anonymous1378]

Try

=BYROW(X2#,LAMBDA(_a,
SUM(FILTER(Table1[Sales],
(Table1[Date]>=_a)*
(Table1[Date]<=EOMONTH(--_a,0))
))))

 

[Fluff]

Why can't you use sumifs like

=sumifs(Table1[Sales],Table1[Date],">="&X2#,Table1[Date],"<="&EOMONTH(--X2#,0))