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:
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:
I have created separate date tables and date columns in this table but have still had errors.
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).
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(),
, 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.