Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
Anonymous
Not applicable

Do calculation on different granularity and show results in different Granularity

‎09-18-2021 10:35 PM

Hi Guys,

 

I need support on below calculation.

 

I need to show difference between Downtime_start and Downtime_end for Each ID. But the calculation should happen based on below conditions.

 - Under one ID you will have different downtime start times, 
 - for Each Start time you have different modified times

 

So you need to do the calculation for unique Downtime_start and you need to consider the Downtime_start with Highest Modified time and for each unique Downtime_start you need to calculate the difference between start and end time and show the total in ID level.

 

Hope the requirement is clear.

This is the sample data.

 

PBI file link https://drive.google.com/file/d/1rzL-rMAly6YHCmOwsHdXfSKa9GdR7hLQ/view?usp=sharing

 

Thank you very much in Advance.

 

 

Modified timeIDDonwTime_StartDownTime_End
15/09/2021 6:07:14 AM7512/09/2021 6:07 AM         13/09/2021 6:11 AM
15/09/2021 6:08:45 AM7512/09/2021 6:07 AM15/09/2021 6:11:48 AM
15/09/2021 6:12:15 AM7501/09/2021 6:11 AM13/09/2021 6:11 AM
15/09/2021 6:14:00 AM7501/09/2021 6:11 AM15/09/2021 6:11:48 AM
15/09/2021 6:17:10 AM7510/09/2021 6:16 AM15/09/2021 6:16:21 AM
15/09/2021 6:17 :15 AM7510/09/2021 6:16 AM15/09/2021 6:16:21 AM


This is my current formula which gives me the correct result at Start date level in the report. But when I view the result in ID level this is not giving me the correct result.

 

Total Downtime =
VAR _max_modified_date =
CALCULATE(
MAX ( Issue_Tracker[Modified] ),
ALL(Issue_Tracker),
VALUES(Issue_Tracker[DonwTime_Start])
)
VAR _selected_id =
SELECTEDVALUE (Issue_Tracker[ID])
VAR _start_time =
CALCULATE (
SELECTEDVALUE ( Issue_Tracker[DonwTime_Start] ),
FILTER (
Issue_Tracker,
Issue_Tracker[Modified] = _max_modified_date &&
Issue_Tracker[ID] = _selected_id
)
)
VAR _end_time =
CALCULATE (
SELECTEDVALUE ( Issue_Tracker[DownTime_End] ),
FILTER (
Issue_Tracker,
Issue_Tracker[Modified] = _max_modified_date &&
Issue_Tracker[ID] = _selected_id
)
)
VAR _result =
DATEDIFF ( _start_time, _end_time, DAY )
RETURN
_result
1,885 Views