Month on Month Error with sporadic data

symmo22 — Wed, 15 Feb 2023 21:47:52 GMT

HI,

Would welcome some help with a formula i've been strugging to get right. I have a report pulling commodity data from various sources. As an example, USDA. I have a report guiding users to select a commodity showing price history, some smart narrative and key headlines such as MoM, QoQ, YoY. The latter just isn't right, however. I feel like it is due to the fact the pricing is monthly, and I just have a date field set to the first of the month, linked to a calendar table. I then have measures set up such as:

Value MoM% USDA =

VAR __PREV_MONTH = CALCULATE(SUM(COM_USDA[Value]), DATEADD('Calendar'[Date], -1, MONTH))

RETURN

DIVIDE([Latest Price USDA] - __PREV_MONTH, __PREV_MONTH)

I suppose what I need is a formula that takes the last month's average (there is a lag to data so won't always be the current calendar month), and compare's it to one month/quarter/year prior. I just can't for the life of me figure it out.

Appreciate any help!

Re: Month on Month Error with sporadic data

symmo22 — Thu, 16 Feb 2023 09:09:58 GMT

For further detail, here's the desired output. As you can see from the chart - 11% is nowhere near correct. The measure is in a card with the 'top 1 latest date' filter added, and no interation with date slider.

I have another dataset from the IMF where it seems to work - not sure why this is any different.

topic Re: Month on Month Error with sporadic data in DAX Commands and Tips

Month on Month Error with sporadic data

Re: Month on Month Error with sporadic data