Forum Discussion

GillyGils's avatar
GillyGils
Frequent Visitor
4 years ago
Solved

LAG Conversion from MDX to DAX

Hi All,

I've the following MDX formula i'm trying to convert to DAX:

([Time Dimension].[Date].CurrentMember.lag(1), [Measures].[Total Available])

 

I've been trying to adjust the code below so I can retrieve the previous months 'Total Available' value but i'm only seem to be just getting the one singular previous month value:

PrevMonth = CALCULATE(
[Total Available],
ALLEXCEPT(TEST_TABLE,TEST_TABLE[DATE]),
DATEADD([DATE],-1,MONTH)
)

Any pointers would be appreciated.

Cheers

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi GillyGils ,

    If there are only 'End of Month Dates' in your fact table. Please have a try.

    Create a measure.

    Measure1 = 
VAR _date =
MAX ( 'Table'[date] )
VAR _pre_date =
EOMONTH ( _date,-1)
VAR _value =
CALCULATE (
[total available],
FILTER ( ALL ( 'Table'), 'Table'[date] = _pre_date )
)
RETURN
_value

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi GillyGils ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

    Measure =
VAR _date =
    MAX ( 'Table'[date] )
VAR _pre_date =
    EDATE ( _date, -1 )
VAR _value =
    CALCULATE (
        [total available],
        FILTER ( ALL ( 'Table'[date], 'Table'[total] ), 'Table'[date] = _pre_date )
    )
RETURN
    _value

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without  priavcy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • GillyGils's avatar
    GillyGils
    Frequent Visitor
    4 years ago

    Hi Polly,

    The code you provided is working for me as expected. I had use to EOMONTH, as I only have 'End of Month Dates' in my fact table:

     

    Measure =
    VAR _date =
    MAX ( 'Table'[date] )
    VAR _pre_date =
    EOMONTH ( _date, -1 )
    VAR _value =
    CALCULATE (
    [total available],
    FILTER ( ALL ( 'Table'[date], 'Table'[total] ), 'Table'[date] = _pre_date )
    )
    RETURN
    _value

     

    One quick additonal question, when I drag in a Date Hierarchy of Time Dimension, the values are then empty:

    Do I add another FILTER conidition to the DAX query?

     

    Thanks

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi GillyGils ,

      If there are only 'End of Month Dates' in your fact table. Please have a try.

      Create a measure.

      Measure1 = 
VAR _date =
MAX ( 'Table'[date] )
VAR _pre_date =
EOMONTH ( _date,-1)
VAR _value =
CALCULATE (
[total available],
FILTER ( ALL ( 'Table'), 'Table'[date] = _pre_date )
)
RETURN
_value

      Best Regards

      Community Support Team _ Polly

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • GillyGils's avatar
        GillyGils
        Frequent Visitor
        4 years ago

        Thank you Polly. This works perfectly.

        Still getting a hang of the DAX syntax and this is helping me understand it more.

         

        Thank you.