topic Re: DAX to calculate difference beetween monthly average value of current months vs. 12 months ago in DAX Commands and Tips

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

InnaBer — Fri, 05 Aug 2022 16:05:57 GMT

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

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

tamerj1 — Fri, 05 Aug 2022 17:49:05 GMT

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 )

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

OliT — Tue, 09 Aug 2022 08:09:16 GMT

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

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

OliT — Tue, 09 Aug 2022 08:09:17 GMT

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

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

InnaBer — Tue, 09 Aug 2022 11:00:21 GMT

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

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

InnaBer — Tue, 09 Aug 2022 11:01:10 GMT

Good day

i will try this solution,thank you

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

InnaBer — Fri, 12 Aug 2022 14:41:57 GMT

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