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.
Hi
When using the MoM% quick measure Power BI is also returning values for the last month PLUS 1. I.e. If my last period is May 2017, then the quick measure calculates the MoM% for June 2017 which creates a distortion when looking at the chart/data.
Have anybody come across this issue?
Thanks,
Solved! Go to Solution.
@Anonymous
Hi, made a adjustment to the quick measure. Add before a condition to evaluate the Date.
Value MoM%-Modified = If (VALUES(Table1[Date])<>BLANK(), IF( ISFILTERED('Table1'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), VAR __PREV_MONTH = CALCULATE(SUM('Table1'[Value]), DATEADD('Table1'[Date].[Date], -1, MONTH)) RETURN DIVIDE(SUM('Table1'[Value]) - __PREV_MONTH, __PREV_MONTH) ))
Regards
Victor
Lima - Peru
You can also use below DAX in a measure instead of quick measure to solve the distortion in the beginning as well as at the end of the visuals. Here you just have to create one more measure for prior month instaed of defining a variable within the measure itself.
Current_Month_Value = SUM('Table1"[Value])
Prior_Month_Value = CALCULATE('Table1'[Value], PREVIOUSMONTH('Table1'[Date]))
%_MoM_Modified_Value =
IF(
AND(
'Table1'[Value]<>Blank(),
'Table1'[Prior_Month_Value]<>Blank()
),
DIVIDE([Value],[Prior_Month_Value],Blank())-1,
Blank()
)
@Anonymous
Hi, made a adjustment to the quick measure. Add before a condition to evaluate the Date.
Value MoM%-Modified = If (VALUES(Table1[Date])<>BLANK(), IF( ISFILTERED('Table1'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), VAR __PREV_MONTH = CALCULATE(SUM('Table1'[Value]), DATEADD('Table1'[Date].[Date], -1, MONTH)) RETURN DIVIDE(SUM('Table1'[Value]) - __PREV_MONTH, __PREV_MONTH) ))
Regards
Victor
Lima - Peru
Why
VALUES(Table1[Date])<>BLANK() not works for me? it show error message, you expeted return a sing value. So I changed values to Max or Min
Thanks so much Victor!
This worked just prefect !
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |