Forum Discussion
Previous date dax or M
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,
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.
2 Replies
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"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.
