Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show running total

Hey,

I made a graph that shows the running total to the last 3 years.

Here is how I did the measure:

Measure 2 = CALCULATE( SUM(DWH_GlobalSalesLong[Line_Value_Dollars]),FILTER(ALLSELECTED(DWH_GlobalSalesLong),DWH_GlobalSalesLong[Invoice_Month]<=MAX(DWH_GlobalSalesLong[Invoice_Month]) ))

Also, please see attached photo:

As you can see, each year starts with the total of the end of last year (last December).

I would like to make a compersion between those 3 years, which means that the graph will show 3 different lines but they all start in (0,0).
Thanks
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a date table

    date = ADDCOLUMNS(CALENDAR(DATE(2016,1,1),DATE(2020,12,31)),"year",YEAR([Date]))

add columns
year-month = FORMAT([Date],"yyyymm")
month = FORMAT([Date],"Mmm")

    Show running total for last 3 years:

    Measure 3 = IF(MAX([year])>YEAR(TODAY())-3,CALCULATE(SUM(Sheet3[value]),DATESYTD('date'[Date],"12/31")))

     

    or show last 3 years' running total for each year,

    for 2018, running total is from 2016-2018

    for 2019, is from 2017-2019

    Measure =
VAR lastdate1 =
    LASTDATE ( 'date'[Date] )
RETURN
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER (
            ALLSELECTED ( 'date' ),
            'date'[year-month] <= MAX ( 'date'[year-month] )
                && DATEDIFF ( 'date'[Date], lastdate1, YEAR ) < 3
                && DATEDIFF ( 'date'[Date], lastdate1, YEAR ) >= 0
        )
    )


Measure 2 = IF(MAX([year])>YEAR(TODAY())-3,[Measure])

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies