Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate UsageTime per Day

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

 

 

 

Date
time
Usage Metric
utilization

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        ValtteriN
        Community Champion
        4 years ago

        Hi,

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