Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX - Calculating Averages per hour

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!

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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