Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Quick Measure MoM%

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,

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks so much Victor! 

      This worked just prefect !

    • Younglee's avatar
      Younglee
      New Member
      3 years ago

      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

       

  • bi_expt08's avatar
    bi_expt08
    Regular Visitor
    4 years ago

    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()
    )