Forum Discussion

LukeB's avatar
LukeB
Advocate I
3 years ago
Solved

Lag function in DAX

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

 

Need Help

4 Replies

    • LukeB's avatar
      LukeB
      Advocate I
      3 years ago

      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!

    • LukeB's avatar
      LukeB
      Advocate I
      3 years ago

      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! 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?