Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Looking for help subtracting same row dates.
From the example dataset below, I'm looking to subtract "Completed On" dates that have statuses SFT which are followed by REA.
For example, for Equip_Num 367584264, I need the Date difference between 5/24/2022 and 5/17/2022.
It’s important that I only subtract only rows with status SFT only followed by REA.
Thank you. Below is what the dataset would look like.
Equip_Num | Status | Completed On | Date_Difference |
367584264 | SFT | 5/1/2022 | |
367584264 | RVW | 5/13/2022 | |
367584264 | SFT | 5/17/2022 | |
367584264 | REA | 5/24/2022 | |
367584265 | RVW | 5/11/2022 | |
367584265 | SFT | 5/15/2022 | |
367584265 | REA | 5/20/2022 | |
367584266 | RVW | 5/2/2022 | |
367584266 | SFT | 5/3/2022 |
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzN7UwMTIzUdJRCnYLAZKm+ob6RgZGRkqxOqjSQWHhEGlj7PJw7eY49Ls6guWNTDDlTZHNx2K/KbL5pjj0w8w3wJQ3QzLfCLs0zHiY72IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equip_Num = _t, Status = _t, #"Completed On" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Completed On", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Equip_Num", Order.Ascending}, {"Completed On", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Date_Difference", each try if [Equip_Num]=#"Added Index"{[Index]-1}[Equip_Num]
and [Status]="REA"
and #"Added Index"{[Index]-1}[Status] ="SFT"
then [Completed On]-#"Added Index"{[Index]-1}[Completed On]
else null otherwise null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date_Difference", Int64.Type}})
in
#"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Hi @NPC,
You may create a Measure as well.
Date Difference =
VAR Prev_SFT_Date =
CALCULATE (
MAX ( 'Table'[Completed On] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
'Table'[Completed On] < MAX ( 'Table'[Completed On] )
&& 'Table'[Status] = "SFT"
&& MAX ( 'Table'[Status] ) = "REA"
)
)
VAR Prev_Date =
CALCULATE (
MAX ( 'Table'[Completed On] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
'Table'[Completed On] < MAX ( 'Table'[Completed On] )
)
)
VAR Diff =
CALCULATE (
DATEDIFF ( Prev_SFT_Date, MAX ( 'Table'[Completed On] ), DAY ),
FILTER ( 'Table', Prev_SFT_Date = Prev_Date )
)
RETURN
IF ( ISBLANK ( Diff ), "", Diff )
Then, the result looks like this.
Also, attached the pbix as reference.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Thank you Ibendlin. Initially I didn't want to go the advanced editor route because the dataset has many other columns that I need for other measures. I didn't mention this in my post because I wanted to focus to be on this only and I realize now that I should. However, a combination of what you and v-cazheng-msft provided gave me the solution I was looking for, so thank you!
Hi @NPC,
You may create a Measure as well.
Date Difference =
VAR Prev_SFT_Date =
CALCULATE (
MAX ( 'Table'[Completed On] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
'Table'[Completed On] < MAX ( 'Table'[Completed On] )
&& 'Table'[Status] = "SFT"
&& MAX ( 'Table'[Status] ) = "REA"
)
)
VAR Prev_Date =
CALCULATE (
MAX ( 'Table'[Completed On] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
'Table'[Completed On] < MAX ( 'Table'[Completed On] )
)
)
VAR Diff =
CALCULATE (
DATEDIFF ( Prev_SFT_Date, MAX ( 'Table'[Completed On] ), DAY ),
FILTER ( 'Table', Prev_SFT_Date = Prev_Date )
)
RETURN
IF ( ISBLANK ( Diff ), "", Diff )
Then, the result looks like this.
Also, attached the pbix as reference.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi v-cazheng-msft, thank you! For some reason i'm getting zeros. Not sure what the issue is but I'll spend somethime to look. Seems like it worked for you.
Hi @NPC,
Sorry that I didn’t make the sample pbix file clear. In the sample pbix file, you can see two tables. Query1 table is generated with the M query from lbendlin. The Index column is required because the calculation is based on it.
In the another table, I make calculation with DAX Measure on its sample data.
May I know on what kind of rows you get zeros?
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
An index column is mandatory. Otherwise Power BI doesn't know what you mean by "previous row" etc.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzN7UwMTIzUdJRCnYLAZKm+ob6RgZGRkqxOqjSQWHhEGlj7PJw7eY49Ls6guWNTDDlTZHNx2K/KbL5pjj0w8w3wJQ3QzLfCLs0zHiY72IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equip_Num = _t, Status = _t, #"Completed On" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Completed On", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Equip_Num", Order.Ascending}, {"Completed On", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Date_Difference", each try if [Equip_Num]=#"Added Index"{[Index]-1}[Equip_Num]
and [Status]="REA"
and #"Added Index"{[Index]-1}[Status] ="SFT"
then [Completed On]-#"Added Index"{[Index]-1}[Completed On]
else null otherwise null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date_Difference", Int64.Type}})
in
#"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.