Conditional formatting based on formula - previousyear calculation

Maieev — Thu, 15 Feb 2024 09:29:16 GMT

Hi guys,

I have a table - Sales, where I have Actual ASP and Revenue columns. I wrote a formula to be able to show conditional formatting - icons based on the difference between years. It works for Revenue but doesn't for Actual ASP. They are in the same table, with the same dates. The only difference is that in column Revenue there are no blanks, and in Actual ASP blanks are possible and they are there.

Measure that works:

Icon Revenue = VAR _val = CALCULATE ( SUM ( Sales[Revenue] ) ) - CALCULATE ( SUM ( Sales[Revenue] ), PREVIOUSYEAR ( Sales[Date] ), ALL(Sales) ) RETURN IF ( ISBLANK ( _val ) || SELECTEDVALUE('Sales'[Time.Fiscal Year Name]) = "21/22", BLANK (), -- If previous year's data is not available or it's for 21/22 fiscal year, return blank to turn off conditional formatting SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 ) )

And that doesn't work:

Icon ASP = VAR _val = CALCULATE ( SUM ( Sales[Actual ASP] ) ) - CALCULATE ( SUM ( Sales[Actual ASP] ), PREVIOUSYEAR ( Sales[Date] ), ALL(Sales) ) RETURN IF ( ISBLANK ( _val ) || SELECTEDVALUE('Sales'[Time.Fiscal Year Name]) = "21/22", BLANK (), -- If previous year's data is not available or it's for 21/22 fiscal year, return blank to turn off conditional formatting SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 ) )

I also tried to connect that to calendar table and take date from there but still no result. I broke down formula and wanted to check that PreviousYear calculation but it doesn't show results in the table:

PreviousYearASP = CALCULATE ( SUM ( Sales[Actual ASP] ), PREVIOUSYEAR ( Sales[Date] ) )

As you can see there are values. I don't know why one measure works and the other just with column name changed not. Their format in the table is exactly the same.

Could you please help with some ideas?

Re: Conditional formatting based on formula - previousyear calculation

Maieev — Thu, 15 Feb 2024 14:37:59 GMT

I've managed to figure it out!

I needed to use SAMPERIODLASTYEAR function and use it with separate Calendar Year - also in the visualization I changed the Year field to the one from the Calendar table.

Icon Revenue = VAR _val = CALCULATE ( SUM ( Sales[Revenue] ) ) - CALCULATE ( SUM ( Sales[Revenue] ), SAMEPERIODLASTYEAR( ( 'Calendar'[Date] ) )) RETURN IF ( ISBLANK ( _val ) || SELECTEDVALUE('Sales'[Time.Fiscal Year Name]) = "21/22", BLANK (), -- If previous year's data is not available or it's for 21/22 fiscal year, return blank to turn off conditional formatting SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 ) )

It works great :).

topic Re: Conditional formatting based on formula - previousyear calculation in DAX Commands and Tips

Conditional formatting based on formula - previousyear calculation

Re: Conditional formatting based on formula - previousyear calculation