Previous month % - error

[lucie_rabochova]

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. Sometimes % is missing etc.

Does anybody know what I'm doing wrong? Can somebody help please?

I'm attaching PowerBI Desktop file: https://drive.google.com/file/d/1MR9BP7BwVnnF_pH26VqLhHfNwVaTLTqF/view?usp=drive_link 

[talespin]

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]

Hi talespin,

thank you for your answer.

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

[talespin]

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

https://drive.google.com/file/d/1yYlhgvVbfzqg-6FUT3zX7H2_9jyePmws/view?usp=sharing

[lucie_rabochova]

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]

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]

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]

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 · Accepted solution]

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.

[Joe_Barry]

Hi @lucie_rabochova 

Without seeing how your measures are written it is difficult, but the first thing I would do is put the Date in the Columns section of the visual

Share your measures if you need further assistence

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[lucie_rabochova]

Hi Joe,

I have shared desctop file, its here https://drive.google.com/file/d/1MR9BP7BwVnnF_pH26VqLhHfNwVaTLTqF/view?usp=drive_link