Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
symmo22
Frequent Visitor

Month on Month Error with sporadic data

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! 
1 REPLY 1
symmo22
Frequent Visitor

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. 

symmo22_0-1676538529314.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.