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