Skip to main content
cancel
Showing results for 
Search instead 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

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

 

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
2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
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] )
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 ) )

jdbuchanan71_0-1632035780804.png

 

View solution in original post

Fowmy
Super User
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

Fowmy_0-1632036333912.png

 



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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

9 REPLIES 9
Fowmy
Super User
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

Fowmy_0-1632036333912.png

 



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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

jdbuchanan71
Super User
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] )
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 ) )

jdbuchanan71_0-1632035780804.png

 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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