LAG Conversion from MDX to DAX

[GillyGils]

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

[GillyGils]

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 · Accepted solution]

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]

Thank you Polly. This works perfectly.

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

Thank you.

[Anonymous]

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.