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
jillw
Frequent Visitor

Rolling Average of Days Open

For KPI's we do a weekly rolling average for the amount of days that a record is open. We are currently doing this measure manually in excel by checking and calculating each week but want to move to an automatic system PowerBI.

 

The chart that we generate in excel looks like this:

avgdayschart.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have tried using both PowerBI quickmeasure as well as almost every answer I could find on a forum to generate this table in Power BI but kept having errors, such as date must be "power bi provided date heirarchy". A big dilemma is that although we have a "days open" column, that number changes each day for records that are still open, but is final once that record is closed. 

 

The data I currently have in Power BI looks like this:

 

powerbitable.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have created separate date tables and date columns in this table but have still had errors. 

 

Any help would be appreciated!

1 ACCEPTED SOLUTION


@jillw wrote:

I've used the exact code you provided and had no errors, but all the values it returns are blanks.


Hmm, that's strange. Here is a link to a file with that code working using the sample data from your earlier post.

 

2019-03 average days open.png

View solution in original post

5 REPLIES 5
d_gosbell
Super User
Super User

 I had a look at the data you pasted and can't see how it arrives at the image you posted.

 

Can you explain exactly how your are calculating your rolling average? For example if I was to create a 3 month rolling average, the amount for March would be (Jan + Feb + Mar) / 3  and for Apr it would be (Apr + Mar + Feb) / 3   etc. How many weeks are you rolling together in your average? Or are you doing something different?

 

It would also be helpful if you posted a small amount of sample data (as text, not as an image) and then posted the output you would expect to be calculated from that sample data (along with the logic used).

Sorry, the data I pasted from Power BI was not used to create the chart.

 

I have created a new table in excel that arrives at the image posted. The data for the presented chart is posted below:

 

Record ID#StatusDate CreatedClosed Date03/07/201902/28/201902/21/201902/14/2019
620In-Progress3/7/2019     
619Closed3/5/20193/7/20192   
618In-Progress3/4/2019 3   
617In-Progress3/1/2019 6   
616In-Progress3/1/2019 6   
615In-Progress3/1/2019 6   
614In-Progress2/21/2019 147  
613Closed2/21/20193/6/2019 7  
612In-Progress2/19/2019 1692 
611In-Progress2/15/2019 20136 
610In-Progress2/13/2019 221581
609In-Progress2/13/2019 221581
607In-Progress2/12/2019 231692
606In-Progress2/11/2019 2417103
605Closed2/11/20192/13/2019    
604In-Progress2/8/2019 2720136
603Closed2/8/20193/7/20192720136

 

The date in the fifth column is for one week ago =Today()-7 and the next column is two weeks ago etc. 

 

The data below each date is found by using the following equation:

=IF(AND(($C2) < (TODAY()-7),(OR($D2>=TODAY()-7,(ISBLANK($D2))))), (TODAY()-7-$C2), "")

 

I then created a pivot table based on this data and calculated the average for each week's column to provide me with the following chart, similar to the one in the original message:

 

ucc.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Back to my main issue:

The first four columns are data that is provided to PowerBI from online reports produced in QuickBase. 

I want to be able to produce a weekly rolling average chart, much like the one above, within PowerBI for a report containing this and many other KPI's. 

Creating a date matrix, like the one above, is not my most desirable option as I want to go back two years.

 

I hope this helps to clarify, thank you for responding. 

So for this to work you will need a separate table of dates. You can create a basic one using a calculated table with the following sort of formula to get all the dates in 2018 and 2019

Date = CALENDAR( DATE(2018,1,1), DATE(2019,12,31))

 

Then the following measure should work

 

Average Open Days = 
VAR _currentDate = SELECTEDVALUE('Date'[Date])
VAR _selectedRolling7Days = IF( MOD((_currentDate - TODAY() )/7,1)=0,_currentDate)
RETURN if (NOT(ISBLANK(_selectedRolling7Days)) && _selectedRolling7Days <= TODAY(),
    AVERAGEX(
        filter(Opportunites
            , Opportunites[Date Created] < _selectedRolling7Days 
            && (Opportunites[Closed Date] >= _selectedRolling7Days || ISBLANK(Opportunites[Closed Date]))
        )
        ,var _closedDate = IF(ISBLANK(Opportunites[Closed Date]),_selectedRolling7Days,Opportunites[Closed Date])
        return _closedDate - Opportunites[Date Created].[Date]
    )
)

The one "tricky" bit here is the _selectedRolling7Days variable, I'm using some math to figure out if the difference between TODAY() and each value in 'Date'[Date] is equally divisible by 7 to only return every 7th date starting from TODAY(). For other dates this variable will return a blank value so we don't calculate a value for those dates.

I've used the exact code you provided and had no errors, but all the values it returns are blanks.


@jillw wrote:

I've used the exact code you provided and had no errors, but all the values it returns are blanks.


Hmm, that's strange. Here is a link to a file with that code working using the sample data from your earlier post.

 

2019-03 average days open.png

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.