Forum Discussion
Cumulative data and Filter
Hi Everyone!
I want to combine cumulative data and a filter of month in a report so that I could show in a graph or table the sum of amount till the month that I filtered.
I mean, if I filter by May I don´t want to see only May, I want to show January, February and May.
There is any way?
Thanks!
Anonymous
With the slicer visual, you cannot do it now. There is no way to create a dynamic month column/table according to your selection in the slicer.
However, you can try to use the Timeline visual. Just drag the progress bar to include January to May.
I create a calendar table and a cumulative measure with following formulas. And create relationship between Calendar and Table1 with Date key.
Calendar = CALENDAR ( "1/1/2016", "12/31/2016" )
Cumulative = IF ( SUM ( Table1[Number] ) <> BLANK (), CALCULATE ( SUM ( Table1[Number] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) )Best Regards,
Herbert
2 Replies
v-haibl-msftMicrosoft Employee
Anonymous
With the slicer visual, you cannot do it now. There is no way to create a dynamic month column/table according to your selection in the slicer.
However, you can try to use the Timeline visual. Just drag the progress bar to include January to May.
I create a calendar table and a cumulative measure with following formulas. And create relationship between Calendar and Table1 with Date key.
Calendar = CALENDAR ( "1/1/2016", "12/31/2016" )
Cumulative = IF ( SUM ( Table1[Number] ) <> BLANK (), CALCULATE ( SUM ( Table1[Number] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) )Best Regards,
Herbert