Forum Discussion
Advanced filtering and aggregation problem
I'd like to create a calculated table ( i think using SUMARIZE) from a table called BILLING
that returns COMPANYNAME
, city, state
, a column named FinalDate (where finaldate is somewhere between now and 12 months in the future)
, a sum of the last 12 months RECEIPT by companyname
I have a column called MONTHSAGO which is zero based and can be used to filter the last 12 months of records,
but i do not understand how to add the filter which will show only totals where the FINALDATE is between now and 12 months in the future. is that a filter all or something else?
=SUMMARIZE(BILLING
,BILLING[CompanyName]
,BILLING[CITY]
,BILLING[STATE]
,BILLING[FINALDATE]
, "RECEIPTTOTAL", calculate(
sum(BILLING[RECEPT])
,Filter (
(BILLING),
(BILLING[MONTHSAGO]) < 12
)
)
)
2 Replies
It's not quite clear to me what your data table looks like. Is there only one MONTHSAGO value for each FINALDATE?
If so, does this work for what you're after?
SUMMARIZECOLUMNS ( BILLING[CompanyName], BILLING[CITY], BILLING[STATE], BILLING[FINALDATE], FILTER ( VALUES ( BILLING[MONTHSAGO] ), BILLING[MONTHSAGO] < 12 ), "RECEIPTTOTAL", CALCULATE ( SUM ( BILLING[RECEPT] ) ) )Hi,
Why do you want to create a calculated table? Why not an actual visual? Calculated tables do not respond to changes in slicer while visuals do.
