DAX - Calculating Averages per hour

[Anonymous]

Hi,

I am trying to calculate the average number of active documents at any given hour in a specific date range. (In the date range, 01/01/18 - 11/02/18, how many active documents have there been, on average, at 11:00?). I can get this to work for 1 day at a time, but cannot get it to do it over the date range.

On my report it is the graph I am trying to get to work, the 2 tables are essentially just my workings out/tests. I know the documents created/completed measures (counts and averages) are correct, but not the active documents measures.

My pbix file is https://drive.google.com/file/d/13PouI5wPiBIbaUD2CdLwUO2fgzeXZvZ-/view?usp=sharing

The trend should look like the Active Docs per day column in the image below.

Thanks for your help!

[v-chuncz-msft]

@Anonymous 

You may try using AVERAGEX.

• https://community.powerbi.com/t5/Desktop/monthly-average-of-daily-sums/m-p/291451#M128396
• https://community.powerbi.com/t5/Desktop/Percentage-to-goal-by-dynamic-date-range/m-p/642778#M307965

[Anonymous]

@v-chuncz-msft 

Thanks for replying. Unfortunately averagex doesn't quite work in this situation since the table I would be using is one full of measures. I essentially need to find the sum of the number of active documents at each hour and then divide that by the number of days in the date range.

I can do the second bit and divide by the number of days, but I cannot seem to find the sum per hour.

The red in the picture shows the measures. The count T1 is the measure I am interested in and it works when I have both dates and times on the table, but when I take the date column out, the measure sets the values to 0.

The DAX I am using is as follows

ActivePerHourCountTest1 = 
CALCULATE(
    CALCULATE(
        COUNTA(IMSDocuments[Unique Document Id]) + 0,
        FILTER(
            IMSDocuments, 
            IMSDocuments[Creation Date] + IMSDocuments[Creation Hour] <= SELECTEDVALUE(Dates[Date]) + SELECTEDVALUE(Times[Time])
        ), 
        FILTER(
            IMSDocuments, 
            OR(
                ISBLANK(IMSDocuments[Completion Date]),
                IMSDocuments[Completion Date] + IMSDocuments[Completion Hour] > SELECTEDVALUE(Dates[Date]) + SELECTEDVALUE(Times[Time])
            )
        )
    ),
    CROSSFILTER(Dates[Date], IMSDocuments[Creation Date], NONE)
)  

 For this date range, the table/count should sum up the values for both days. The second image should look like this