How to get correct total for MoM Measure

[Anonymous]

I have a measure that calculates MoM change. I would like to get the total MoM Change by Year. However, I noticed that the total is extremely far off the correct calculation.
The correct total MoM change should be 1835, however, PowerBI shows 1,087,518 which is way off. Then then noticed the error is caused by the fact that PowerBI calculated the MoM change everyday of each month when i included days for drill down as shown in the attached picture.

However, my raw data was actually only for the entire month, instead of individual days of the month.

This is the date column for my raw data:

As you can see, the dates are all the 1st of each month only.

This is the DIM calendar table that I use for creating my measures:

Using Calendar = CALENDARAUTO()

I suspect this is the main problem. Would like help to solve this! Thanks!

[amitchandak]

@Anonymous , can share some details. Can you share sample data and sample output in table format?

Hope you are trying like these. with a date table .

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

	


diff = [MTD Sales]-[last year MTD Sales]
diff % = divide([MTD Sales]-[last year MTD Sales],[last year MTD Sales])

refer : https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,
So basically i tried to calculate MoM change using this formula:

MoM Change =
VAR PrevMonth = CALCULATE(SUM(Table1[Capacity]),PREVIOUSMONTH('Calendar'[Date]))
VAR MoM = SUM(Table1[Capacity]) - PrevMonth
Return
MoM

For Calendar[Date], it is basically a Calendar table i created using:

Calendar = CALENDARAUTO()

Then i made a relationship between the new Calendar[Date] Column and my original data's date column Table1[Period]

In my original data, the dates are all 1st of each month only, because the data is meant to be monthly data. 

However, I think due to the CalendarAuto function, new dates were created and the calculation was messed up by including the new dates.

I would like to know how do I calculate the MoM Change without all the new dates. You can see from my screenshot of the 2 tables, when i drill down by days in the bottom table, there are dates for 2nd Jan, 3rd Jan, etc.

Basically I want the top table, and when i drill down, there arent any other dates that did not exist in the original dates column (I.e. anything that is not 1st of each month)

Does this help you understand my issue? Thanks!