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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Vahid DM,

Thanks for your prompt reply but apologies I realised that in addition to below 2 columns there is also 3rd key column which is version number meaning every week a new version is received that has all historic data so data is as below

 

ID               Date                Version

A               11/11/2021        20

B                10/11/2021        20

C                12/11/2021        20

A                17/11/2021         21

B                 08/11/2021        21

A                 17/11/2021        22

B                  03/11/2021       22

B                  15/11/2021       23

So I'm trying to compare end date of each task with its previous end date & version for e.g Task A Version 22 end date to be matched with Task A Version 21 & it's end date , Task A Version 21 & end date to be matched with Version 20 & it;s end date, if dates match task to show it has been completed else not completed.

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

 

 

Lastly I was wondering if someone can also help me with my another query where I want to upload word document in power bi, use it as cover page it has tables and columns & we need to amend the data in these columns every time.

Many Thanks

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:)