Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to calculate a running average of a measure based on a year as its starting point. The company wants to use this formula to calculate “delivery rate”.
(Annual) Delivery rate = [Running Total # of Cases Delivered ] / [ Running Average # of open cases at the end of each month ]
I was able to create a measure to calculating the running total of cases delivered and it works fine:
Running Total Delivery Count = CALCULATE( [Delivery Count], FILTER( ALL('Date'[Date]), AND(MAX('Date'[Date]) >= 'Date'[Date] , YEAR('Date'[Date]) = 2022 )))
But I am having trouble creating the measure for the running average of orders. This is my attempt:
Running Avg Open Cases = AVERAGEX(FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) && YEAR([Date]) = 2022), [Open case])
But the number it did not give my correct numbers. I don’t know if it maybe have something to do with the measure ([Open case]), also being based on the date field.
Open case =
var maxDate = MAX( 'Date'[Date])
return
CALCULATE( COUNTROWS(Cases), ALL('Date'),
Cases[Order Date] <= maxDate && (Cases[Delivered Date] > maxDate || ISBLANK(Cases[Delivered Date]) ))
Reference: Solved: Re: Count the number of rows that satisfies date f... - Microsoft Power BI Community
Ultimately, I want to be able to create a new measure that involves dividing the two separate measures to get the proposed delivery rate. See table below. The idea is A / B = C
Help on this would be greatly appreciated. Please and thank you.
Year | Month | Cases Delivered | Running Total of Cases Delivered (A) | Open Cases (headcount) | Expected Running Avg Headcount (rounddown) (B) | Actual Running Avg Headcount | Delivery Rate (C) |
2022 | Jan | 19 | 19 | 1294 | 1294 | 1290 | 1.5% |
2022 | Feb | 15 | 34 | 1311 | 1302 | 1297 | 2.6% |
2022 | Mar | 19 | 53 | 1323 | 1309 | 1307 | 4.0% |
2022 | Apr | 15 | 68 | 1321 | 1312 | 1310 | 5.2% |
2022 | May | 22 | 90 | 1326 | 1315 | 1314 | 6.8% |
2022 | Jun | 9 | 99 | 1335 | 1318 | 1318 | 7.5% |
2022 | Jul |
| 99 | 1335 | 1320 | 1320 | 7.5% |
2022 | Aug |
| 99 | 1335 | 1322 | 1322 | 7.5% |
2022 | Sep |
| 99 | 1335 | 1323 | 1324 | 7.5% |
2022 | Oct |
| 99 | 1335 | 1325 | 1325 | 7.5% |
2022 | Nov |
| 99 | 1335 | 1325 | 1326 | 7.5% |
2022 | Dec |
| 99 | 1335 | 1326 | 1326 | 7.5% |
Solved! Go to Solution.
@cferv_77 , Try like
calculate(AVERAGEX(values('Date'[Month Year]), [Open case]),FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) && YEAR([Date]) = 2022))
refer if needed
Hello @amitchandak ,
I am having trouble with the DAX formula when I change the year to 2021. It works fine when I put it in a table, but when I use it in a bar visual keeps giving me the total number for that year instead of for that month.
Any suggestions to fix this?
@cferv_77 , Try like
calculate(AVERAGEX(values('Date'[Month Year]), [Open case]),FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) && YEAR([Date]) = 2022))
refer if needed
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |