Conditional formatting based on formula - previousyear calculation

[Maieev]

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?

[Maieev · Accepted solution]

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 :).