Previous date dax or M

[EDO_01_1789]

Hello 

yes I would like your help about a probleme

I have put below a table of what I want :

Fichier Excel	Validator Name	Validation date	Previous validator date
Excel File  A	Validator n° 1	11/08/2022
Excel File  A	Validator n° 2	12/08/2022	11/08/2022
Excel File  A	Validator n° 3	13/08/2022	12/08/2022
Excel File  A	Validator n° 4	14/08/2022	13/08/2022
Excel File  A	Validator n° 5	15/08/2022	14/08/2022
Excel File  B	Validator n° 1	02/08/2022	15/08/2022
Excel File  B	Validator n° 2	03/08/2022	02/08/2022
Excel File  B	Validator n° 3	04/08/2022	03/08/2022
Excel File  B	Validator n° 4	05/08/2022	04/08/2022
Excel File  B	Validator n° 5	06/08/2022	05/08/2022
Excel File  C	Validator n° 1	01/07/2022	06/08/2022
Excel File  C	Validator n° 2	02/07/2022	01/07/2022
Excel File  C	Validator n° 3	03/07/2022	02/07/2022
Excel File  C	Validator n° 4	04/07/2022	03/07/2022
Excel File  C	Validator n° 5	05/07/2022	04/07/2022

I can't create the last column in M or DAX.

if anyone could help me it would be very useful

I just want to make sure that the validator 1 of each excel file does not have a previous date.

The validator 2 must have the validation date of the validator 1
The validator 3 must have the validation date of the validator 2
Validator 4 must have the validation date of validator 3
The validator 5 must have the validation date of the validator 4

Thanks for your help,

[v-yalanwu-msft · Accepted solution]

Hi, @EDO_01_1789 ;

You could create a measure by dax.

Measure = 
CALCULATE(MAX('Table'[Validation date]),
FILTER(ALL('Table'),[Fichier Excel]=MAX('Table'[Fichier Excel])&&[Validation date]<MAX('Table'[Validation date])))

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdExCoNAFATQqyxbu/D3765JmwQ9go1YSLQQlgRCihwrZ8jJtBwccNN+XjEzv+9t87nP2bRLns3FVrYb8zKN7+fLPH5f47eLiqqTs/PeDlXBK3ot+4A+lH1EH8s+oU97fz3sK5SfPfYVys8e+wrlZ4995Y/82Ffqvb8d9D05of+yV/S0D/uAnvZhH9HTPuwT+m2fYQU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fichier Excel" = _t, #"Validator Name" = _t, #"Validation date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fichier Excel", type text}, {"Validator Name", type text}, {"Validation date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    BuffList = List.Buffer(#"Changed Type"[Validation date]),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Previous validator date", each try BuffList{[Index]-1} otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"