Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Visualize cumulative value

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. 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    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.

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    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.