count number of days selected in filter

[Anonymous]

Hello!

I'm trying to create report in PowerBi that will show service uptime in percents. I have 2 tables: "Date" and "Notices" with information about services interruption in hours (column Duration). 

I created a mesure that gives me service uptime, but it's not working correctly if i chose two dates from the visual filter on report.

Measure = (CALCULATE(COUNTROWS('Date'),'Date'[Date])*24 - SUM(Notices[Duration]))/(CALCULATE(COUNTROWS('Date'),'Date'[Date])*24)

Could you please help me to create measure that will give correct uptime depending on dates selected in filter. 

I.e. if i chose range 10.05.2022 - 15.05.2022 with no notices about interruption in this dates it will return 100%. And so on.

Thanks!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

First, please make sure that there is one relationship created between Date and Notices tables base on Date fields. You can update the formula of Measure as below and check if it can get the correct result...

Measure =
VAR _fhours =
    COUNTROWS (
        FILTER (
            'Date',
            'Date'[Date] >= MIN ( 'Date'[Date] )
                && 'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    ) * 24
RETURN
    DIVIDE ( _fhours - SUM ( Notices[Duration] ), _fhours )

If the above one can't help you get the desired result, please provide some sample data in your Notices table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi  @v-yiruan-msft , 

yes, your Measure did provide desired result. Thanks a lot!

But i also solved it by done this:

1) Created conditional column in table "Date" called "Boolean" that eaual 1 if Date column not empty.

2) Changed my measure from original post to this one:

Measure = (CALCULATE(COUNTROWS('Date'),'Date'[Boolean]=1)*24 - SUM(Notices['Duration']))/(CALCULATE(COUNTROWS('Date'),'Date'[Boolean]=1)*24) 

But anyway thanks again for the help!