Forum Discussion

Maieev's avatar
Maieev
Resolver I
2 years ago
Solved

Conditional formatting based on formula - previousyear calculation

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 f...
DAX help needed
  • Maieev's avatar
    Maieev
    2 years ago

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

Maieev's avatar
Maieev
Resolver I
2 years ago

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