Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
my column (Dias Totais) has the following formula:
Solved! Go to 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"
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
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.
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"
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
@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))
@yforti Can you please paste sample table and clarify what you mean by 'when a previous data is different'? What is your desired result?
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:
@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.
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
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"
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]
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @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?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.