CODEWITHSUNDEEP
excelexcel-formulapowerbidaxbusiness-intelligence
MmVv
MmVv

Reputation: 553

PowerBI: How to calculate/convert Time into Percentage using DAX/Measure

I managed to get in Excel desired % of time difference from column E, easy job just changed the Data Type to Percentage. What are we calculating is % of these TimeDifferences, one per one (other columns inconsiderable).

The same thing isn't in PowerBI, where I am not able to calculate it properly, always getting "1" before comma and then the result - you can compare it in both tables/columns what I am talking about.

I am looking for the way/DAX/measure how to properly calculate it, no matter in decimals or directly to percentage, as long as the % is the same as in Excel column. Any ideas?

P.S Left is Excel and right is PowerBI!

enter image description here

Upvotes: 0

Views: 954

Answers (1)

Aldert
Aldert

Reputation: 4313

Seems Excel is basing the percentage on 24 hours, this I used in the calculation (24 hours = 24 * 3600 seconds).

I started combining in power query the date and the time, this has to do with the fact that you go over the day and your calculation still needs to be correct.

Go to query editor. select both columns, combine them. Next change the type to Date/Time, result:

enter image description here

Save and close editor.

In Power Bi, add a column:

NextDate = LOOKUPVALUE(Explog[Date];Explog[Index];Explog[Index] + 1)

This is picking up the next Date based on Index + 1

Add another column TimeDiffSec, calculating the datediff in seconds:

TimeDiffSec = DATEDIFF(Explog[Date];Explog[NextDate];SECOND)

Last step is adding a column for percentage:

% of time difference = 
var perc = Explog[TimeDiffSec]/ (24*3600)
return if(perc >= 1; perc -  1; perc)

End result:

enter image description here

Note: If you have a situation you do not want to mix the System (STYRAX - scrubber) you can use the following for the NextDate:

NextDate = 
var nextIndex = 
    CALCULATE(MIN(Explog[Index]);
        FILTER(Explog;Explog[Index] > EARLIER(Explog[Index]) && Explog[System] = EARLIER(Explog[System])))
return 
    LOOKUPVALUE(Explog[Date];Explog[Index];nextIndex; Explog[System];Explog[System])

Upvotes: 1

Related Questions