Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks v-haibl-msft

 

It isn't the solution that I expect but It's an amazing alternative.

 

Thanks!