Calculate UsageTime per Day

[janiBI]

Hello, 

I'm new to Power BI and need your help:

I have a table with licenese and now I want to calculate their Utilization per Day.

There are 2 licenses(BIE-WV414 and BIE-WV450) with licenseStart time and licenseEnd time, per day there are many licenses started and finsihed. I also have calculated the usagetime (in total).

The problem is some of the licenses are used longer than 24 hours or past midnight. (See Screenshot). 

Now i want to calculate and display the usage time of each license per day.

For example for the first line: 

1.10 BIE-WV414  aprox. 22h

2.10 BIE-WV414 24h

3.10 BIE-WV414 aprox. 7min

Regards, 

Jani

[ValtteriN]

Hi,

This was quite fun dax to write. Here is an example on how to do this with a measure:

Data:

Dax:

Time =

var c_date = MAX('Calendar example'[Date])

var _sdate = max(LicenseTime[LicenseStart])

var _edate = max(LicenseTime[LicenseEnd])

var dimdate = FILTER('Calendar',and('Calendar'[Date]<=_sdate && _sdate <= 'Calendar'[Date]+1 ,

'Calendar'[Date]<=_edate && _edate <='Calendar'[Date]+1))

var d_start = MINX('Calendar','Calendar'[Date])

var d_end = d_start+1

var _start = MAX(_sdate,d_start)

var _end = min(_edate,d_end)

var result =

sumx(LicenseTime,DATEDIFF(_start,_end,HOUR))

return

if(result<0,0,result)

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[janiBI]

Hi @ValtteriN,

the solution is looking good, but i am not able to use your dax code flawless.

Do I need to create an extra Date table?

[ValtteriN]

Hi,

Yeah, you would need a calendar table to make this work. 

[janiBI]

Hello @ValtteriN,

thanks for your reply, i keep getting wrong numbers calculated. 

So I rebuilt your tables and then it works fine. The problem is when there are more than one entries for one date, the numbers are getting calculated wrong. Can you help me with this problem?