Forum Discussion
Month over Month change
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.
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.
6 Replies
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:
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.Thank you for your reply!
Balance is a field. There is only one balance for each month.
