Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Offset function giving wrong results for first month in X Axis

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 
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      3 years ago

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

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous  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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    The measure can be simplied to

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