Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I need to calculate the sum of timedifference in below scenario:
I need to calculate the sum of difference between start date & end date of step = A (take minimum start date for each type) and Step = B (take minimum end date for each type). for example: difference between start date & end date of row 1 and 2, and difference between start date & end date of row 4 and 6. Record inside step are static (always A, B) but type can be more. In this case it will be 01:10:00
I am having difficulties due to blank values in start date and end date. Not able to pick the correct record.
can anyone give some suggestion?
Thanks
Solved! Go to Solution.
If you are looking for a DAX measure, you could use a formula like this:
Duration in Minutes =
VAR _step_type =
SUMMARIZECOLUMNS (
'Table'[Type],
"StartA",
CALCULATE (
MIN ( 'Table'[Start] ),
'Table'[Step] = "A",
ALLEXCEPT ( 'Table', 'Table'[Type] )
),
"EndB",
CALCULATE (
MIN ( 'Table'[End] ),
'Table'[Step] = "B",
ALLEXCEPT ( 'Table', 'Table'[Type] )
)
)
RETURN
SUMX ( _step_type, DATEDIFF ( [StartA], [EndB], MINUTE ) )
Hi @Taro_Gulat ,
I create a table as you mentioned.
Then I think you can create a calculated column and here is the DAX code.
Column =
VAR _MinStartDate =
CALCULATE (
MIN ( 'Table'[Start Date] ),
FILTER (
'Table',
'Table'[Step] = "A"
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
)
)
VAR __MinEndDate =
CALCULATE (
MIN ( 'Table'[End Date] ),
FILTER (
'Table',
'Table'[Step] = "B"
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
)
)
RETURN
DATEDIFF ( _MinStartDate, __MinEndDate, MINUTE )
If you want to change the Column's format, you can do this step.
Column 2 =
VAR _TotalTime =
SUMX (
SUMMARIZE ( 'Table', 'Table'[Type], "UniqueColumn", MAX ( 'Table'[Column] ) ),
[UniqueColumn]
)
RETURN
FORMAT ( INT ( _TotalTime / 60 ), "00" ) & ":"
& FORMAT ( MOD ( _TotalTime, 60 ), "00" ) & ":"
& FORMAT ( 0, "00" )
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Taro_Gulat ,
I create a table as you mentioned.
Then I think you can create a calculated column and here is the DAX code.
Column =
VAR _MinStartDate =
CALCULATE (
MIN ( 'Table'[Start Date] ),
FILTER (
'Table',
'Table'[Step] = "A"
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
)
)
VAR __MinEndDate =
CALCULATE (
MIN ( 'Table'[End Date] ),
FILTER (
'Table',
'Table'[Step] = "B"
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
)
)
RETURN
DATEDIFF ( _MinStartDate, __MinEndDate, MINUTE )
If you want to change the Column's format, you can do this step.
Column 2 =
VAR _TotalTime =
SUMX (
SUMMARIZE ( 'Table', 'Table'[Type], "UniqueColumn", MAX ( 'Table'[Column] ) ),
[UniqueColumn]
)
RETURN
FORMAT ( INT ( _TotalTime / 60 ), "00" ) & ":"
& FORMAT ( MOD ( _TotalTime, 60 ), "00" ) & ":"
& FORMAT ( 0, "00" )
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you are looking for a DAX measure, you could use a formula like this:
Duration in Minutes =
VAR _step_type =
SUMMARIZECOLUMNS (
'Table'[Type],
"StartA",
CALCULATE (
MIN ( 'Table'[Start] ),
'Table'[Step] = "A",
ALLEXCEPT ( 'Table', 'Table'[Type] )
),
"EndB",
CALCULATE (
MIN ( 'Table'[End] ),
'Table'[Step] = "B",
ALLEXCEPT ( 'Table', 'Table'[Type] )
)
)
RETURN
SUMX ( _step_type, DATEDIFF ( [StartA], [EndB], MINUTE ) )
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |