topic Re: Previous month % - error in DAX Commands and Tips

Previous month % - error

lucie_rabochova — Sat, 23 Mar 2024 12:33:49 GMT

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

Re: Previous month % - error

Joe_Barry — Sat, 23 Mar 2024 14:48:06 GMT

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

Re: Previous month % - error

lucie_rabochova — Sat, 23 Mar 2024 18:00:57 GMT

Hi Joe,

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

Re: Previous month % - error

talespin — Sun, 24 Mar 2024 08:02:17 GMT

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)

Re: Previous month % - error

lucie_rabochova — Sun, 24 Mar 2024 08:31:41 GMT

Hi talespin,

thank you for your answer.

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

Re: Previous month % - error

talespin — Sun, 24 Mar 2024 13:33:57 GMT

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

Re: Previous month % - error

lucie_rabochova — Sun, 24 Mar 2024 16:03:54 GMT

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.

Re: Previous month % - error

lucie_rabochova — Mon, 25 Mar 2024 09:42:25 GMT

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!

Re: Previous month % - error

talespin — Tue, 26 Mar 2024 04:43:11 GMT

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)

))

Re: Previous month % - error

lucie_rabochova — Tue, 26 Mar 2024 13:51:14 GMT

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?

Re: Previous month % - error

talespin — Wed, 27 Mar 2024 03:00:34 GMT

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.