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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yforti
Helper II
Helper II

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
Jimmy801
Community Champion
Community Champion

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
yforti
Helper II
Helper II

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

Jimmy801
Community Champion
Community Champion

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

V-pazhen-msft
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.
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Jimmy801
Community Champion
Community Champion

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
Jimmy801
Community Champion
Community Champion

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

Jimmy801
Community Champion
Community Champion

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.

Jimmy801
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors