DAX to calculate difference beetween monthly average value of current months vs. 12 months ago

[InnaBer]

Good day 

Could you please advise how would you solve pbix task?

I have database with daily prices, I specified date as market year month, market year name

I created the dashboard with monthly average values

I would like to prepare dashboard of below format but

with monthly spread between years to be returned

so in cell 18/19 Jul I want to show the result of 10706 – 10474 = 232 {value}

In excel i would create monthly average table>>added and calculated column Value -12month>> and calculated the difference

How would you advise to do it in Power BI ?

Thank you in advance

[InnaBer · Accepted solution]

thank you all for help

The solution i used eventially is not that elegant but probably it will be helpful for someone

Since the target was to show MoM variation between different years (2018 vs 2017, 2019 vs. 2018, 2020 vs. 2019)

Firstly is created new table

=

SUMMARIZE(Data,Data[Month num],Data[Parameter name],"2017",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2017),"2018",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2018),"2019",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2019),"2020",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2020),"2021",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2021),"2022",CALCULATE(AVERAGE(Data[Value]),Data[Year]=2022))

then i simply calculated the variation in columns

[OliT]

Hi @InnaBer 

>> so in cell 18/19 Jul I want to show the result of 10706 – 10474 = 232 {value}

Are 17/18 & 18/19 measures? If you try measure= [18/19]-[17/18], will it work? If not, could you share the sample file for us to check? Thanks.

Regards,

OliT

[OliT]

Hi @InnaBer 

>> so in cell 18/19 Jul I want to show the result of 10706 – 10474 = 232 {value}

Are 17/18 & 18/19 measures? If you try measure= [18/19]-[17/18], will it work? If not, could you share the sample file for us to check? Thanks.

Regards,

OliT

[InnaBer]

Good day OliT

pls note 17/18 & 18/19 are not measures, it is just matrix table result

pls see sample [download the sample] file attched: i replaced values with random value betweeen 10000 and 15000 to not violate original source rights. The table i look into is [Data]

thank you for you help

[tamerj1]

Hi @InnaBer 
Please try

MyMeasure =
VAR CurrentYear =
    MAX ( TbaleName[Year] )
VAR CurrentValue =
    SUM ( TbaleName[Value] )
VAR PreviousValue =
    CALCULATE (
        SUM ( TbaleName[Value] ),
        TbaleName[Year] = CurrentYear - 1,
        REMOVEFILTERS ( TableName )
    )
VAR LastSelectedYear =
    CALCULATE ( MAX ( TbaleName[Year] ), ALLSELECTED ( TableName ) )
RETURN
    IF ( CurrentYear = LastSelectedYear, CurrentValue - PreviousValue, CurrentValue )

[InnaBer]

Good day

i will try this solution,thank you