DAX help

[Anonymous]

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               Date 

A               11/11/2021

B                10/11/2021

C                12/11/2021

A                14/11/2021

B                 08/11/2021

A                 17/11/2021

B                  03/11/2021

B                  15/11/2021

[VahidDM · Accepted solution]

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/

[Anonymous]

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]

Hi,

Please claearly show the end result that you are expecting.

[Anonymous]

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 · Accepted solution]

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]

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

[Ashish_Mathur]

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

[VahidDM · Accepted solution]

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/

[smpa01]

@Anonymous  what is the desired output ?