Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@Anonymous
You may try using AVERAGEX.
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
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |