Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi.
Please view the below example table, which also includes the desired result to the right.
You will notice each ID has mutiple Statuses and each have their own Start Date and End Date. I need to calculate the number of Days for each ID, for each Status with a unique starting number (ignore the accompanying letter).
For example, ID 1 has the statues 1a, 1b, and 1c. I need to calculate the number of Days between the earliest date for Status starting with '1' and the latest date for Status starting with '1'. This would be 1a 01/04/2021 through to (including) 1c 10/08/2022. This is a total of 497 Days.
Note, the starting Status doesn't always start with the same Status value. Look at ID 1 Status 3 it starts with 3c, but for ID 3 the Status 3 starts with 3d. Point being, we don't want to look for a particular Status value exact (i.e. 3c) but we want to group by the Status starting number (i.e. 1b would be series 1, 2a would be the series 2, etc..). The desired results, to the right of the exmaple table, will confirm the wanted result.
I plan to drop these values into a horizontal bar-graph, so Series would be on the X-axis, ID on the Y-axis and the count will be the Days. I'm not sure if I should have this calculated in the measure of a column for my needs.
Please can someone help me with this calculation?
Thanks.
Solved! Go to Solution.
Hi @D_PBI ,
If I understand your issue, please try this.
My example:
Period =
Var _id = MAX('Status'[ID])
Var _series = MAX('Status'[Series])
var _MinDate = CALCULATE(min('Status'[Start Date]),FILTER(All('Status'),'Status'[ID]= _id && 'Status'[Series]=_series))
var _MaxEndDate = CALCULATE(MAX('Status'[End Date]),FILTER(All('Status'),'Status'[ID]= _id && 'Status'[Series]=_series))
var _dateDif= DATEDIFF(_MinDate,_MaxEndDate,DAY)
Return _dateDif
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
@D_PBI ,
You are very welcome!
Nathaniel
Proud to be a Super User!
Hi @D_PBI ,
If I understand your issue, please try this.
My example:
Period =
Var _id = MAX('Status'[ID])
Var _series = MAX('Status'[Series])
var _MinDate = CALCULATE(min('Status'[Start Date]),FILTER(All('Status'),'Status'[ID]= _id && 'Status'[Series]=_series))
var _MaxEndDate = CALCULATE(MAX('Status'[End Date]),FILTER(All('Status'),'Status'[ID]= _id && 'Status'[Series]=_series))
var _dateDif= DATEDIFF(_MinDate,_MaxEndDate,DAY)
Return _dateDif
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
61 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |