Forum Discussion

lucie_rabochova's avatar
lucie_rabochova
Helper I
2 years ago
Solved

Previous month % - error

Hello, I'm trying to calculate previous month difference % in the attached dashboard. As you can see, the previous month function does not work properly at all and shows strange percentages. Sometim...
"DAX help"
"DAX"
% difference
% Growth
  • talespin's avatar
    talespin
    2 years ago

    hi lucie_rabochova 

     

    Sorry I am not aware why this happens, even though there is nothing wrong with measure, I think I started seeing this issue with PowerBI Feb 24 release which I am currently using.

talespin's avatar
talespin
Solution Sage
2 years ago

hi lucie_rabochova 

 

Your measure is comparing current month with previous month value and I checked at Date level and looks fine to me.

 

As for the Blank% there is no data for previous month. For ITA-City there is no data for 2023-02

 

I used below measure.

 

Views MoM% =
VAR _SelectedYear = SELECTEDVALUE(DimDates[Year])
VAR _SelectedMonth = SELECTEDVALUE(DimDates[Month])
VAR _PrevYearMonth = EOMONTH(DATE(_SelectedYear, _SelectedMonth, 1), -1)
VAR V = CALCULATE([Views], REMOVEFILTERS(DimDates), DimDates[Year] = YEAR(_PrevYearMonth) && DimDates[Month] = MONTH(_PrevYearMonth))
RETURN DIVIDE([Views]-V, V)
lucie_rabochova's avatar
lucie_rabochova
Helper I
2 years ago

Hi talespin,

thank you for your answer.

Can you please share the correct measure? Because I'm getting the below error, thanks

 

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi lucie_rabochova 

     

    Please use measure as it is, sorry I forgot to mention that you need to add Month column to your date table.

    Please add this as calculated column to your Date table.

    MONTH(DimDates[Date])

     

    Views MoM% =
    VAR _SelectedYear = SELECTEDVALUE(DimDates[Year])
    VAR _SelectedMonth = SELECTEDVALUE(DimDates[Month])
    VAR _PrevYearMonth = EOMONTH(DATE(_SelectedYear, _SelectedMonth, 1), -1)
    VAR V = CALCULATE([Views], REMOVEFILTERS(DimDates), DimDates[Year] = YEAR(_PrevYearMonth) && DimDates[Month] = MONTH(_PrevYearMonth))
    RETURN DIVIDE([Views]-V, V)
     
    pbix file
    • lucie_rabochova's avatar
      lucie_rabochova
      Helper I
      2 years ago

      Thank you, it looks much better, one more thing, sometimes I can see strange total and subtotal % (see screenshot) I was trying to hide it (using special column) but it is still showing, is there any option to hide it? It is very confusing for our PowerBI users.

       

      • lucie_rabochova's avatar
        lucie_rabochova
        Helper I
        2 years ago

        I have found a solution for total nad subtotal % - I just hid it using white colour :-). I just wonder if it is possible to create a % only between the rows that are displayed? For example print screen showing 2024-01 and 2024-03, so the % should be difference between 01 and 03 and not 02 and 03, if you understand me.

         

        Thank you so much for your help!