Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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]
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |