Lag function in DAX

[LukeB]

Hello 

Looking for some help if possible.

I am trying to write some DAX in DAX studio to return the previous cancelled date for an account for a broker (however not getting very far).

What I am trying to achieve is the below - where I can return the prev cancelled date (Column H) from the data stored in column B for the same broker.

I am currently writing DAX against a shared dataset which holds a lot of data as we snapshot all our accounts everyday.

This is where I have gotten to so far with my DAX as I have seen online I needed to use the EARLIER function - but I just cannot get the "Prev Cancelled Date" column to work. I assume it's do with my FILTER function inside that calculated column but to be honest its just confusing me.

EVALUATE
CALCULATETABLE (
    SUMMARIZE (
        FactCase,
        DimCase[Account Number],
        FactCase[Cancelled Date],
        DimBroker[Broker First Name],
        DimBroker[Broker Last Name],
        DimBroker[Broker Email],
        FactCase[Snapshot Date],
        "Row Count", [Row Count],
        "Prev Cancelled Date",
            CALCULATE (
                MAX ( FactCase[Cancelled Date] ),
                FILTER (
                    FactCase,
                    DimBroker[Broker Email] = EARLIER ( DimBroker[Broker Email] )
                        && DimBroker[Broker First Name] = EARLIER ( DimBroker[Broker First Name] )
                        && DimBroker[Broker Last Name] = EARLIER ( DimBroker[Broker Last Name] )
                        && FactCase[Cancelled Date] < EARLIER ( FactCase[Cancelled Date] )
                )
            )
    ),
    FactCase[Snapshot Date] = DATEVALUE ( "2023-07-12" ),
    FactCase[Cancelled Date] <> BLANK ()
)
ORDER BY DimBroker[Broker Email], FactCase[Cancelled Date] DESC

As you can see below from using the DAX I have provided for the "Prev Cancelled date" column, I am returning blank data for that whole column.

I do have some sample data if required but it won't let me upload to this message.

Any help that could be given would be greatly appreciated

Thanks

[Greg_Deckler · Accepted solution]

@LukeB See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[shreep1]

Hello @Greg_Deckler @LukeB,

My 1st column has a list of Equipment, and the 2nd column has the date/time they failed in the date/time format. For each of these equipment, I am trying to find the difference between date/time they failed (difference between rows) and calculate their summation at the end.

I created the Index column and wrote the following DAX code to solve this issue:

DIFF =

var _lasttime = CALCULATE(MAX('Sampling'[BreakDownDate]),FILTER('Sampling','Sampling'[Index]=EARLIER('Sampling'[Index])-1))

return

DATEDIFF(_lasttime,'Sampling'[BreakDownDate],MINUTE)

It is giving me the right number if I just select(filter) just 1 equipment but as I keep adding Equipments to the table it gives me wrong summation of Breakdown date towards the end. As a whole on 2000+ different equipments, my numbers are not that far. But individually for those equipments my numbers are sharply and significantly different for many equipments. 

Can you please suggest a better DAX code than above to resolve this issue further?

[LukeB]

@Greg_Deckler Actually I got it working with the below from your article. I removed the "Prev Decline" column from inside the summarize and then added wrapped it in a ADDCOLUMNS further down which did the trick.

Thanks a lot for the help! 

[LukeB]

@Greg_Deckler Thanks for coming back so quick.

So looking at the article you sent it adds a calculated column into the physical dataset as per the below screenshot?

Is there a way to do it just where I can create that column inside my below DAX query? As what I am looking for is quite bespoke to a single report and don't want it going into the general dataset.

Apologies if I have missed the point here - DAX confuses the life out of me!