subtract dates in the same rows

[NPC]

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

[lbendlin · Accepted 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".

[NPC]

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!

[v-cazheng-msft · Accepted solution]

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

[NPC]

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.

PREVIEW

 I see from the pbix file that you had a second table with an index column. Should I be doing that perhaps?

[v-cazheng-msft]

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

[lbendlin]

An index column is mandatory. Otherwise Power BI doesn't know what you mean by "previous row" etc.

[lbendlin · Accepted 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".