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

Difference between dates

Hi guys!

My current model already calculates the difference in days between the dates, but the calculation is repeating for equal dates.

I need a column to only bring the difference in days when a previous data is different

Capturar123123.JPG

my column (Dias Totais) has the following formula:

Dias totais =
VAR last =maxx(FILTER(Planilha1,Planilha1[DT_ATUALIZACAO]<EARLIER(Planilha1[DT_ATUALIZACAO])&&Planilha1[Key]=EARLIER(Planilha1[Key])),Planilha1[DT_ATUALIZACAO])
return
if(ISBLANK(last),0,Planilha1[DT_ATUALIZACAO]-last)
 
1 ACCEPTED SOLUTION

Hello @yforti 

 

here now a solution that consideres also your second request. And for next time please always specify all your requirements at the beginning, thanks

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdJUelWB1aCRgNiIChAT0EkHzrREMBowERQPItrQRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YourDate = _t, Key = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"YourDate", type date}},"de-DE"),
    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(AddIndex, {"Key"}, {{"AllRows", each _, type table [YourDate=date, Key=text]}}),
    AddDifference = Table.TransformColumns(
        #"Grouped Rows" ,
        {
            {
                "AllRows",
                each Table.AddColumn
                (
                    _,
                    "Difference",
                    (add)=> try if add[YourDate]<>_{[Index=add[Index]-1]}[YourDate] then Duration.TotalDays(add[YourDate]-List.Min(_[YourDate])) else null otherwise null
                )
            }
        }
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddDifference, "AllRows", {"YourDate", "Difference"}, {"YourDate", "Difference"})
in
    #"Expanded AllRows"

Jimmy801_0-1609841914921.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

13 REPLIES 13
Helper I
Helper I

Guys, I will try to explain my problem again: For the list below I need to calculate the difference in days per key, that is, when the key changes the difference must be calculated again.

 

bbbb.JPG

Hello @yforti 

 

here now a solution that consideres also your second request. And for next time please always specify all your requirements at the beginning, thanks

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdJUelWB1aCRgNiIChAT0EkHzrREMBowERQPItrQRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YourDate = _t, Key = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"YourDate", type date}},"de-DE"),
    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(AddIndex, {"Key"}, {{"AllRows", each _, type table [YourDate=date, Key=text]}}),
    AddDifference = Table.TransformColumns(
        #"Grouped Rows" ,
        {
            {
                "AllRows",
                each Table.AddColumn
                (
                    _,
                    "Difference",
                    (add)=> try if add[YourDate]<>_{[Index=add[Index]-1]}[YourDate] then Duration.TotalDays(add[YourDate]-List.Min(_[YourDate])) else null otherwise null
                )
            }
        }
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddDifference, "AllRows", {"YourDate", "Difference"}, {"YourDate", "Difference"})
in
    #"Expanded AllRows"

Jimmy801_0-1609841914921.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Community Support
Community Support

@yforti 
I guess you can get it with DAX. You need to first Add an index column in power query, and create the following calculated column.

 

Column = IF([Date]<>MAXX(FILTER('Table',[Index]=EARLIER([Index])-1),[Date]),
DATEDIFF(MAXX(FILTER('Table',[Index]=EARLIER([Index])-1),[Date]),[Date],DAY))

V-pazhen-msft_0-1609477586295.png

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

@yforti  Can you please paste sample table and clarify what you mean by 'when a previous data is different'? What is your desired result?


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

@AllisonKennedy 

As you can see the column (DT_ATUALIZAÇÃO) has repeated dates, this is because there are steps that happen on the same day. When the date changes from 7/2/2019 to 7/3/2019 the result of the day difference between these steps must be 1, however, as you can see in the photo, this calculation is repeated every day 7/3/2019 of the column(Dias totais). I need this calculation to occur only once and not repeatedly.

Desired result:
resul.JPG



@yforti 

 

@Jimmy801  is correct - you need an Index column that tells Power BI when the last action is reached. Is there any column in your data which can help with this? Sort order is not guaranteed, so simply adding an index column in Power Query might not work 100% of the time. 


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hello @yforti 

 

I think this is not possible in DAX in a new column, because I don't know any way to reference to other rows. However in Power Query this can be achieved by adding a Index-column and then reference the index - 1.

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA6VYHWpwjGjGMTSgGicWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YourDate = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"YourDate", type date}},"de-DE"),
    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1),
    AddDifference = Table.AddColumn
    (
        AddIndex,
        "Difference",
        each try if [YourDate]<>AddIndex{[Index=[Index]-1]}[YourDate] then Duration.TotalDays([YourDate]-List.Min(AddIndex[YourDate])) else null otherwise null
    ),
    #"Removed Columns" = Table.RemoveColumns(AddDifference,{"Index"})
in
    #"Removed Columns"

Jimmy801_0-1609321687597.png

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Jimmy, can you not use this code:

AddIndex{[Index]-1}[YourDate]

I don't think this full expression is needed is it?

AddIndex{[Index=[Index]-1]}[YourDate]

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hello @edhans 

 

you are right. If the index column starts with 0 then it's possible to use this code. 

 

BR

 

Jimmy

@Jimmy801 

I applied the solution but the column (difference) didn't bring any results, can you explain why?

Capturarcc.JPG

Hello @yforti 

 

this is because you have to reference your prior step exactly in this code "AddIndex" was my previous step

 [YourDate]<>AddIndex{[Index=[Index]-1]}[YourDate]

you have to replace my AddIndex with your prior step that is callled 

 Jimmy801_0-1609402207608.png

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

@Jimmy801 

Is there a way to apply your solution to each key (column KEY) ? When the key changes the count should start over.

Hello @yforti 

 

no, my code would not work with that requirement. In this case you would need to group your data first, then apply my logic on the grouped table, and then expand it again or to adapt my code where the key is checked as well and where the max is only calculated to dates with that key. But at this point I would reccomend to use a grouping before

 

BR

 

Jimmy

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors