Offset function giving wrong results for first month in X Axis

[gvenkatesh482]

Hi Community, 

Please help me with below . 

The requirement is to substract current value and previous value . I used offset and it worked as expected . However, the External filter (Date field ) is returning wrong results for first month(Jan 2022)  and working properly for other months  . Please see screenshots below for reference and also the DAX measure i created. 

Date filter is mandatory. At the  same time, Expected value for Jan 2022 is 6.50 , but not 60.94 

DAX measure : 

NetInjectionAVerage =

Var a = Average('X'[level_percentage])

var b= CALCULATE(average( 'X'[level_percentage]),OFFSET(-1,ALLSELECTED('Monthly Calendar Table SCI'[Year Month],'Monthly Calendar Table SCI'[Year Month Number]),orderby('Monthly Calendar Table SCI'[Year Month Number],asc)))

return a-b

Screenshot : 

Thanks,

G Venkatesh 

[Ashish_Mathur]

Hi,

The measure can be simplied to

Measure = calculate(Average('X'[level_percentage]),previousmonth('Monthly Calendar Table SCI'[Date]))

[gvenkatesh482]

You are right @AllisonKennedy  . I think i was desperate to try a new DAX expression OFFSET when I designed the measure before . It worked now well with Dateadd . 

Thanks for the help . 🙂 

[AllisonKennedy]

@gvenkatesh482  Glad it worked! Can you mark this as solved please so others can find the solution quickly? 

[AllisonKennedy]

@gvenkatesh482  Try using ALL instead of ALLSELECTED

Also, I think you can achieve the same result using DATEADD instead of OFFSET, provided your date table is marked as a date table.