Visualize cumulative value

[Anonymous]

Hi, 

I want to visualize cumulative sales and cumulative budget in an area chart.  

In the same report I have also a date filter. When I use the date filter, example 1-1 to 1-4 then I get as expected only jan-april. 

I wonder if its is possible to see all month and cumulative budget and that only cumulative sales changes when using the datefilter. 

So if I filter the date 1-1/1-4 then I want to see cumulative sales for month jan/april and cumulative budget for whole year. 

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I create a sample using calculated column and measures you can reference. If the result is not what you want , please post your simulative data and your expected output.

I have two tables. There is no relationship between them. And I use the table “Date” to be a date slicer.

1. Create a calculated column in the table in Table1. Choose Table1 à click Modeling à New Column

yearmonth = YEAR(Table1[Month Start]) * 100 + MONTH(Table1[Month Start])

1. Create measures. Choose Table1 à click Modeling à New Measure.

filter sales =

VAR sta  =    MIN ( 'Date'[Month Start] )

VAR endd =    MAX ( 'Date'[Month Start] )

VAR YMs  =    YEAR ( sta ) * 100  + MONTH ( sta )

VAR yme  =    YEAR ( endd ) * 100 + MONTH ( endd )

RETURN

IF(MAX(Table1[yearmonth])<= yme && MAX(Table1[yearmonth])>=YMs,

    CALCULATE (

        SUM ( Table1[Sales] ),

        FILTER (

            ALLSELECTED ( Table1 ),  

            Table1[yearmonth] <= MAX ( Table1[yearmonth] )

                && Table1[Month Start] >= sta

                && Table1[Month Start] <= endd

        )  ))

filter budget =

CALCULATE (

    SUM ( Table1[Budget] ),

    FILTER (

        ALLSELECTED ( Table1[Month Start] ),

        Table1[Month Start] <= MAX ( Table1[Month Start]) && Table1[Month Start] >= MIN('Date'[Month Start])

    ))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.