Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cferv_77
Helper I
Helper I

Calculating Running Average of a Measure

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%

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

View solution in original post

3 REPLIES 3
cferv_77
Helper I
Helper I

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?

amitchandak
Super User
Super User

@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

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

@amitchandak 

Thank you that worked. Much appreciated. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.