Forum Discussion
Month-to-Date Measure Not Working?
Hi all.
So I need to calculate the Month-To-Date cumulative total of Change something like this:
But when I use the Month-To-Date Quick Measure of Power BI, seems like it's not working. But if I use the Year-To-Date Quick Measure, it's working. What I need is the Month-To-Date though. :( Also, I'm getting an error: "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."
Here's the screenshot of my PBI table and the formula:
Change YTD =
IF(
ISFILTERED(notifications[notificationdatetime]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(
SUM(breaches[Change]),
notifications[notificationdatetime].[Date]
)
)
Change MTD =
IF(
ISFILTERED(notifications[notificationdatetime]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALMTD(
SUM(breaches[Change]),
notifications[notificationdatetime].[Date]
)
)THANK YOU!!!!
Hi Anonymous,
1.The TOTALMTD evaluates the value of the expression for the month to date, in the current context.
From screenshot of my PBI table you provide, there is only one piece of data per month without any specific date, then the function ToTALMTD will calculate the data of the whole current month, so the result of measure Change MTD is also correct.
2. If you want to calculate the cumulative total of Change but not the Month -To -Date, you could create the measure below.
MTDChange = CALCULATE ( SUM ( breaches[change] ), FILTER ( ALL ( breaches ), 'breaches'[RETALERS] <= MAX ( 'breaches'[RETALERS] ) ) )You could refer to the picture of the result:
If it is not your desired result, please show your desired output.
Hope it can help you !
Best Regards,
Cherry
4 Replies
Hi Anonymous,
1.The TOTALMTD evaluates the value of the expression for the month to date, in the current context.
From screenshot of my PBI table you provide, there is only one piece of data per month without any specific date, then the function ToTALMTD will calculate the data of the whole current month, so the result of measure Change MTD is also correct.
2. If you want to calculate the cumulative total of Change but not the Month -To -Date, you could create the measure below.
MTDChange = CALCULATE ( SUM ( breaches[change] ), FILTER ( ALL ( breaches ), 'breaches'[RETALERS] <= MAX ( 'breaches'[RETALERS] ) ) )You could refer to the picture of the result:
If it is not your desired result, please show your desired output.
Hope it can help you !
Best Regards,
Cherry
v-piga-msft You're my savior. Thank you so much!! ♥
v-piga-msft You're my savior. Thank you so much!! ♥