Average Cost per Day with conditional formatting

[lkshck]

Hey,

I created an measure for average cost per day, which is shown in a monthly table. In this table I want to use conditional formatting to mark if the previous month is higher, lower or the same. I did the same with SUM and it works fine, but for the average values it doesn't work at some places. Here is the current final table:

As you can see in this example the highlighted yellow value is lower than the previous but it's marked as red (which means higher than the previous). 

Average Cost Measure:

AvgCost = AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost])

Conditional formatting is based on the following conditions:

IconAzAvg Measure:

IconAzAvg = 
VAR _val =
 CALCULATE ( AVERAGE (  'Cost'[Cost] ) ) 
-
 CALCULATE (  AVERAGE ( 'Cost'[Cost] ),
        PREVIOUSMONTH ( 'DateDimension'[Date] )    )
        
RETURN
    SWITCH ( TRUE (),
   _val < 0, -1, 
   _val = 0, 0,
   _val > 0, 1 )

Is there something wrong with the calculation?

[lkshck · Accepted solution]

Got it working with the following Measure:

IconAzAvg = 
VAR _val =
 CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost])  )
-
 CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost]),
        PREVIOUSMONTH ( 'DateDimension'[Date] )    )
        
RETURN
    SWITCH ( TRUE (),
   _val < 0, -1, 
   _val = 0, 0,
   _val > 0, 1 )

[amitchandak]

@lkshck , Check if the previousmonth is working for you. There can few reason it is not working

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...\

Also, try this measure in conditional formatting using the field value option

IconAzAvg =
VAR _val =
CALCULATE ( AVERAGE ( 'Cost'[Cost] ) )
-
CALCULATE ( AVERAGE ( 'Cost'[Cost] ),
PREVIOUSMONTH ( 'DateDimension'[Date] ) )

RETURN
SWITCH ( TRUE (),
_val < 0, "red",
_val = 0, "yellow",
_val > 0, "green" )

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

[lkshck]

PREVIOUSMONTH is already used in the measure. I tried it with the field value option but still the same issues. Some are correct but some formattings are wrong.