Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX help

Hi, I want to write dax for below to extract date before max date for each ID , for e.g ID A - to show the second last date which is 14/11/2021, ID B to show 10/11/2021, please help   ID          ...
Need Help
Tips and Tricks
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this = 

    2nd Max Date =
VAR _MD =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Date] < _MD )
    )

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    These are the calculated column formulas i have used

    Last date of ID = =CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])))
    Second last version = CALCULATE(MAX(Data[Version]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Version]<EARLIER(Data[Version])))
    Date of second last version = CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Version]=EARLIER(Data[Second last version])))
    Status = if(Data[Last date of ID]=[Date of second last version],"Completed","Incomplete")

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Please claearly show the end result that you are expecting.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

So the final result should look like the last Column Task Status, if Tasks end date of recent version matches with the end date previous version by each task , status should show task as completed , else not completed. End column can be derived using calculated column or dax whichever is possible.

 

Task               End Date         Version    Task            End Date           Version         Task Status

A                 17/11/2021        22  with   A                17/11/2021         21          = Completed

 

A                17/11/2021         21  with   A               11/11/2021        20            = Not Completed

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    These are the calculated column formulas i have used

    Last date of ID = =CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])))
    Second last version = CALCULATE(MAX(Data[Version]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Version]<EARLIER(Data[Version])))
    Date of second last version = CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Version]=EARLIER(Data[Second last version])))
    Status = if(Data[Last date of ID]=[Date of second last version],"Completed","Incomplete")

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You are a Star Ashish Mathur this works absolutely fine Thank you so much:)

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        You are welcome.  Thank you for your kind words.  If my previous reply helped, please mark that as Answer.