Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors