cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Do calculation on different granularity and show results in different Granularity

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.

Thank you very much in Advance.

 Modified time ID DonwTime_Start DownTime_End 15/09/2021 6:07:14 AM 75 12/09/2021 6:07 AM 13/09/2021 6:11 AM 15/09/2021 6:08:45 AM 75 12/09/2021 6:07 AM 15/09/2021 6:11:48 AM 15/09/2021 6:12:15 AM 75 01/09/2021 6:11 AM 13/09/2021 6:11 AM 15/09/2021 6:14:00 AM 75 01/09/2021 6:11 AM 15/09/2021 6:11:48 AM 15/09/2021 6:17:10 AM 75 10/09/2021 6:16 AM 15/09/2021 6:16:21 AM 15/09/2021 6:17 :15 AM 75 10/09/2021 6:16 AM 15/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
2 ACCEPTED SOLUTIONS
Super User

@Anonymous

I think something like this will give you what you are looking for.

``````Duration =
VAR _Incidents =
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
ADDCOLUMNS ( _Incidents, "@MaxMod", CALCULATE ( MAX ( 'Table'[Modified time] ) ) )
ADDCOLUMNS ( _AddModified, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ), FILTER('Table', 'Table'[Modified time] = [@MaxMod] ) ) )
RETURN
SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )``````

Although, could you ignore the modified time and just use the max Downtime End for each unique Downtime Start?  That gives me the same answer.

``````Duration Ignore Modified =
VAR _Incidents =
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
ADDCOLUMNS ( _Incidents, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ) ) )
RETURN
SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )``````

Super User

@Anonymous

I created a new measure:

``````Total Downtime Period =
VAR __RESULT =
SUMX(
SUMMARIZE(Issue_Tracker, Issue_Tracker[ID] , Issue_Tracker[DonwTime_Start]),
VAR __MAXMOD = CALCULATE( MAX(Issue_Tracker[Modified time]))
VAR __ENDTIME = CALCULATE( MAX(Issue_Tracker[DownTime_End]) , Issue_Tracker[Modified time] = __MAXMOD )
RETURN
DATEDIFF( Issue_Tracker[DonwTime_Start] , __ENDTIME , DAY )
)
RETURN
__RESULT

``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
9 REPLIES 9
Super User

@Anonymous

I created a new measure:

``````Total Downtime Period =
VAR __RESULT =
SUMX(
SUMMARIZE(Issue_Tracker, Issue_Tracker[ID] , Issue_Tracker[DonwTime_Start]),
VAR __MAXMOD = CALCULATE( MAX(Issue_Tracker[Modified time]))
VAR __ENDTIME = CALCULATE( MAX(Issue_Tracker[DownTime_End]) , Issue_Tracker[Modified time] = __MAXMOD )
RETURN
DATEDIFF( Issue_Tracker[DonwTime_Start] , __ENDTIME , DAY )
)
RETURN
__RESULT

``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

Thank you very much @Fowmy Thank you very much for the support.

Super User

@Anonymous

I think something like this will give you what you are looking for.

``````Duration =
VAR _Incidents =
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
ADDCOLUMNS ( _Incidents, "@MaxMod", CALCULATE ( MAX ( 'Table'[Modified time] ) ) )
ADDCOLUMNS ( _AddModified, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ), FILTER('Table', 'Table'[Modified time] = [@MaxMod] ) ) )
RETURN
SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )``````

Although, could you ignore the modified time and just use the max Downtime End for each unique Downtime Start?  That gives me the same answer.

``````Duration Ignore Modified =
VAR _Incidents =
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
ADDCOLUMNS ( _Incidents, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ) ) )
RETURN
SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )``````

Anonymous
Not applicable

Thank you very much @jdbuchanan71

Anonymous
Not applicable

Looks like this  is giving the correct result I will verify and let you know. Thank you very much for the support @jdbuchanan71

Anonymous
Not applicable

@Anonymous @daxer-almighty

Anonymous
Not applicable
Anonymous
Not applicable
Anonymous
Not applicable

@Greg_Deckler
@Daxer-almighty
@daxer-almighty

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors