Month over Month change

[GoingIncognito]

Hello, all you wise people.

For some reason I've problem writing a measure that gives me MoM change. This should be an easy task.

Sum(balance) gives me the total as it shoud, BUT when ever I insert any time intelligence the measure gives me blank. What could cause this? I do have true and tried calendar table, and I double checked the relation to my fact table. The balance is given only for last day of each month, but this shouldn't be a problem - should it?

I've tried variations of

MoM = sum(balance) - calculate(  sum(balance), dateadd('calendar[date], -1, month) )

MoM = TotalMTD(sum(balance), 'calendar[date])

E: MoM = calculate( sum(balance), previousmonth('calendar[date]) )

but I receive only blanks. 

[v-joesh-msft]

Hi @GoingIncognito ,

Is "balance" a measure or a field? It seems that the writing of sum(balance) is wrong. Other than that, your formula doesn't seem to be wrong. Could you share your sample data?

I created a demo like the following, you can see if there is any difference with your data.

Here is the demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdT57djaTiJHqroo64...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GoingIncognito]

Thank you for your reply!

Balance is a field. There is only one balance for each month.

[GoingIncognito]

[GoingIncognito]

I am still at loss, what am I doing wrong?

Picture above includes the date from fact table, date from calendar table, and following measures

Measure1 = SUM('fact table'[balance])

Measure2 = calculate([Measure1], dateadd('calendar'[date], -1, month)

MoM = [Measure1] - [Measure2]

[v-joesh-msft]

Hi @GoingIncognito ,

Why do you want to use two date columns, try removing the “Fact date” column, or remove the "Calendar date" column and change Measure2:

Measure2 =
CALCULATE ( [Measure1], DATEADD ( 'fact table'[date], -1, MONTH ) )

 Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GoingIncognito · Accepted solution]

I am sincerely thankful for your help!

I think I finally solved this. For some reason the time intelligence didn't work - maybe there's a problem in the data, who knows - but I changed the [last_day_of_the_month] provided in the data to [first_day_of_the_month]. That worked.