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.

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!

 

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi lucie_rabochova 

     

    Please use this measure, it will hide % at BusinessUnit and Theme level.

    It will compare months that are visible, depending on slicer selection.

     

    Views MoM% =
    VAR _SelectedYearMonth = SELECTEDVALUE(DimDates[YearMonthInt])
    VAR _PreviousYearMonth = CALCULATE( MAX(FactChannel[Date]), ALLSELECTED(DimDates[YearMonth]), DimDates[YearMonthInt] < _SelectedYearMonth)
    VAR V = CALCULATE([Views], REMOVEFILTERS(DimDates), DimDates[Year] = YEAR(_PreviousYearMonth) && DimDates[Month] = MONTH(_PreviousYearMonth))
    RETURN IF( ISINSCOPE(DimDates[YearMonth]), DIVIDE([Views]-V, V), BLANK())

     

     

    Added YearMonthInt and Month to date table.

    DimDates =
    VAR BaseCalendar =
            CALENDAR(DATE(2022,8,1), TODAY())
    RETURN
        GENERATE(
            BaseCalendar,
            VAR BaseDate = [Date]
            VAR YearDate = YEAR(BaseDate)
            RETURN ROW (
                "Year",YearDate,
                "YearHalf",YEAR([Date])&" HY "&ROUNDUP(MONTH([Date])/6,0),
                "YearMonth",FORMAT(BaseDate,"yyyy-mm"),
                "YearQuarter", FORMAT(BaseDate,"yyyy") &"-"& FORMAT(BaseDate, "\Qq"),
                "YearMonthDate", FORMAT(BaseDate,"yyyy-mm-dd"),
                "YearMonthInt", INT(FORMAT(BaseDate,"yyyymm")),
                "Month", MONTH(BaseDate)
                ))
    • lucie_rabochova's avatar
      lucie_rabochova
      Helper I
      2 years ago

      Thank you for the excelent solution, it works, I would accept this as solution, just quick question, if I put this measure to the original dashboard with original data, then VARs are underlined in red, but the formula works, any ideas why this is happening?

       

      • talespin's avatar
        talespin
        Solution Sage
        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.