Year Over Year using a baseline Sum dax measure, difficult or impossible?

[Anonymous]

Hi All,

I have a very very difficult dax query and i have tried everything to try get this to work and i am not sure it is possible. The requirement is to use 2019 data as a baseline target and then have a cumulative sum over a period of time using that baseline.

An example,

Say if :

Jan - 2019 = $50

,then Jan 2017, Jan 2021 etc all Januarys would also be $50. This is so i can show it in a line graph against the actuals. i dont think this is possible.

It needs to look like this:

Year - Month

June 2019      $59

July 2019        $33

August 2019   $87

....

June 2020  $59

July 2020    $33

...

June 2025 $59

Current work:

Below i have a cumulative sum of 2019 (

Cummalative Sum 2019 Baseline = CALCULATE(Sum(Emission[Amnt Rand Num]),FILTER(ALL('Date'),'Date'[Date] <= Max('Date'[Date]) && 'Date'[Period_Year] = 2019 )))

However this only works for the period year 2019. (period for 2019 is 01/06/2018 - 31/05/29). 

[v-eqin-msft]

Hi @Anonymous ,

Not very clear... you want to calculate the sum of each year?

Cummalative Sum 2019 Baseline =
CALCULATE (
    SUM ( Emission[Amnt Rand Num] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && 'Date'[Period_Year] = MAX ( 'Date'[Period_Year] )
    )
)

Please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[amitchandak]

@Anonymous , only for 2019 column, create this in your table and date table and join

Emission MMYYY = if(year(Emission[Date],) =2019, format(Emission[Date],"YYYYMM"), blank())

also in date table

Month Year = format(Emission[Date],"YYYYMM")

This will inactive M to M join, use this with use relation

Try to use "Cummalative Sum 2019 Baseline" with this relation