cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NPC
Helper I
Helper I

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
Helper I
Helper I

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors