Do calculation on different granularity and show results in different Granularity

[Anonymous]

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 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

[jdbuchanan71 · Accepted solution]

@Anonymous 

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

Duration = 
VAR _Incidents =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
VAR _AddModified = 
    ADDCOLUMNS ( _Incidents, "@MaxMod", CALCULATE ( MAX ( 'Table'[Modified time] ) ) )
VAR _AddEnd = 
    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] )
VAR _AddEnd = 
    ADDCOLUMNS ( _Incidents, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ) ) )
RETURN
    SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )

[Fowmy · Accepted solution]

@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

[Anonymous]

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

[jdbuchanan71 · Accepted solution]

@Anonymous 

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

Duration = 
VAR _Incidents =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] )
VAR _AddModified = 
    ADDCOLUMNS ( _Incidents, "@MaxMod", CALCULATE ( MAX ( 'Table'[Modified time] ) ) )
VAR _AddEnd = 
    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] )
VAR _AddEnd = 
    ADDCOLUMNS ( _Incidents, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ) ) )
RETURN
    SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )

[Anonymous]

Thank you very much @jdbuchanan71 

[Anonymous]

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

[Anonymous]

@Anonymous @daxer-almighty 

[Anonymous]

@ImkeF @powerbiblogic @Asalk @mussaenda @parry2k @jdbuchanan71 

[Anonymous]

@VahidDM 

[Anonymous]

@Greg_Deckler 
@Daxer-almighty
@daxer-almighty