Forum Discussion

tejasp's avatar
tejasp
Frequent Visitor
5 years ago
Solved

Change Format for Measure

Hi Team,   I have a table that contains data for logging hours on various statuses.     I have created a measure that sums a few of the statuses (Scheduled_Hours = SUM(Schedules[Working H...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi tejasp ,

     

    Thanks for your reply. The default time format in Power BI is 24 hour system. The format you requested is only text format in Power BI, so it cannot be used for calculations. I suggest that you could convert the time format into a decimal format to facilitate calculations. Just like this as follows.

    Scheduled hours_new = 

    SUMX (

     FILTER ( ALL ( 'test_hhnn' ), [Date] = SELECTEDVALUE ( test_hhnn[Date] ) ),

     HOUR ( [D1] ) + HOUR ( [D2] )+(MINUTE ( [D1] ) + MINUTE ( [D2] ))/60

    )

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HarishKM's avatar
HarishKM
Super User
5 years ago

tejasp  Hey mate ,
Kindly refer below image and use that dax formula to fulfill your requirement .

total min =
SUMX ( 'Sheet1', HOUR ( Sheet1[Shift 1 ] ) )
+ TRUNC ( SUMX ( 'Sheet1', MINUTE ( Sheet1[Shift 1 ] ) ) / 60 ) & ":"
& MOD ( SUMX ( Sheet1, MINUTE ( Sheet1[Shift 1 ] ) ), 60 ) & ":00"

 

 

Kudos will be appriciated .

 

 

 

tejasp's avatar
tejasp
Frequent Visitor
5 years ago

HarishKMThank you for your response. By using this dax it tend to show higher number than actual. I think the mod function is causing a trouble, when agent is scheuled HH:30 hours.