cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NPC
Frequent Visitor

subtract dates in the same rows

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_NumStatusCompleted OnDate_Difference
367584264SFT5/1/2022 
367584264RVW5/13/2022 
367584264SFT5/17/2022 
367584264REA5/24/2022 
367584265RVW5/11/2022 
367584265SFT5/15/2022 
367584265REA5/20/2022 
367584266RVW5/2/2022 
367584266SFT5/3/2022 
2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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".

View solution in original post

v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1654581421880.png

 

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

View solution in original post

6 REPLIES 6
NPC
Frequent Visitor

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
Community Support
Community Support

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.

vcazhengmsft_0-1654581421880.png

 

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.

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

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.

vcazhengmsft_0-1654666267445.png

 

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.

lbendlin
Super User
Super User

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".

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors