Formulas

[kalpana]

Hi 

One Column is having date(ddmmyyyy) and other column is having sum of hh:mm:ss. Now i wanna calculate average of hh:mm:ss

for last 7 days..

Please help me 🙂

[Greg_Deckler]

Unfortunately, Power BI does not fully support a true duration data type throughout. So, you are going to have a tough time calculating the average. See the following article for help with that:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Also, vote for the Idea to support this:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

In theory, you could create a column that essentially converted your hh:mm:ss to a number of seconds. You could average that field in a measure and convert it back to hh:mm:ss.

For the average over the last few days, you could create a column like:

DaysAgo = TODAY() - [DateColumn] * 1.

That will give you how many days ago. You could use this in your measure calculation or potentially use time intelligence functions. For example:

Measure = VAR myAverage = CALCULATE(AVERAGE([DurationInSeconds]),FILTER(Table,Table[DaysAgo]<6))
RETURN
//Formula to return hh:mm:ss from myAverage

Psuedocode but should point you down the right path.

[LaurentCouartou]

Durations are just the number of days elapsed between two dates. As a consequence, working with them is really easy: adding, substracting and dividing is all there is to know.

However, unlike Excel, Power BI has no [hh]:mm:ss format.

This means you, you will either have to display your durations as a decimal number (for example: 105.5 hours), or use some text measure (using the FORMAT function or/and concatenation) to display your data.

[kalpana]

I tried this way but am not getting.

[Greg_Deckler]

Can you provide some more information on where you are stuck? I just tried this modified version of the formula from the post and it displayed as expected in a Card visualization.

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = AVERAGE([Year])
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

[kalpana]

How i put measure on the chart

[Anonymous]

Hi @kalpana,

Right click your table in the field list, then click New Measure, add formulas as other's post for this measure. After that, you are able to use the measure in your visuals. There is an example for your reference.


For more details about how to create measure, please review this article:https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-create-measures/ .

Thanks,
Lydia Zhang

[kalpana]

This i got it, but in graph it is not showing

[Anonymous]

Hi @kalpana,

Could you please post expected data and graph here?

Thanks,
Lydia Zhang

[Baskar]

Can u please share some datas. It will help us to understand.

[kalpana]

Data in one column and duration in seconds in other column. Now i should find average of duration in seconds which should be presented in hh:mm:ss.