Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello eveyone!
I've looked online, but can't find the answer anywhere. I might get there with a lot of if's, but I'm hoping there's a more elegant solution.
I need to calculate te period (in days or months) between two dates, but the summermonths (July and August) need to be disregarded.
The problem is that the start and end date can fall in these months, or across years.
Can anyone help? 🙂
Data example:
StartDate |
| EndDate |
3/03/2019 |
| 5/06/2019 |
6/05/2019 |
| 12/08/2019 |
29/08/2019 |
| 8/12/2019 |
30/08/2019 |
| 2/02/2020 |
12/11/2019 |
| 5/03/2020 |
6/06/2020 |
| 9/09/2020 |
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3LDcAwCAPQXTgjmY8ShVlQ9l+jKGma3owfkjPJ4TDRIKaGvuPkpI52ejWMCxaQcWig8COXH1Vf0WRJfaneHb/S12gdTIF4+/kA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}, "de"),
Days = Table.AddColumn(#"Changed Type", "Days Excluding Summer", each let gap=Duration.Days([EndDate]-[StartDate]) in List.Accumulate({1..gap}, 0, (s,c) => if List.Contains({7,8},Date.Month([StartDate]+#duration(c,0,0,0))) then s else s+1))
in
Days
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3LDcAwCAPQXTgjmY8ShVlQ9l+jKGma3owfkjPJ4TDRIKaGvuPkpI52ejWMCxaQcWig8COXH1Vf0WRJfaneHb/S12gdTIF4+/kA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}, "de"),
Days = Table.AddColumn(#"Changed Type", "Days Excluding Summer", each let gap=Duration.Days([EndDate]-[StartDate]) in List.Accumulate({1..gap}, 0, (s,c) => if List.Contains({7,8},Date.Month([StartDate]+#duration(c,0,0,0))) then s else s+1))
in
Days
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you so much!