Days to Next Day with 3 or More Open Appointments, 7 Day Average

[DavidKuhry]

Context:

I need to find the number of days, to the next day with at least 3 open appointments, Averaged over the last 7 days.

Description:

I have a table from our Data Warehouse which contains a bunch of dim keys, and - what I believe to be - the 4 relevant columns.

• DaysToNextApptKey - unique value. Not truly relevant but might be needed

• RefDate

• DateOfService

• OpenSlots

For every RefDate, there are DateOfService days out 6 months. So, I can look at any RefDate and see all the future service dates and how many open appointment slots there are (again, there are also dim keys so I can slice this by person, location, and appointment type). 

There are multiple rows per RefDate, because each RefDate/ServiceDate combination is unique to a provider, location, Business Line, and appointment type, as those are the values we want to eventually slice this measure on.

I need to measure the number of days between EACH RefDate and the EARLIEST DateOfService where OpenSlots >= 3, and do this for the 7 prior days to get an average. The Result for Today() should then be the number of days to the next day with >= 3 open slots, plus the number of days measured from Today()-1, Today()-2... Today()-6 , all divided by 7. I think I'll also need to include a rowcount division in there somewhere too so that I can put this measure into a visual that looks at it by day, week, month, etc. and still get an Average of the Averages. 

Data:

Code So Far:

This code only gets me the days to the next day. I also think it far too complex in doing so. 

_Days to Next Day 3 Open = 

	VAR _SelectedDate = 
    SELECTEDVALUE('Service Date'[Date of Service], [Service Date - Week Ending]) 
	
    VAR _Prior7Date = _SelectedDate - 7
	
	VAR _RefDate7DaysTable = 
    CALCULATETABLE(
		'FACT Open Slots History',
		FILTER(
			ALL('Service Date'),
			'Service Date'[Date of Service] > _Prior7Date && 'Service Date'[Date of Service] <= _SelectedDate
		)
	)

    VAR _NextOpenDate3Slots = 
    CALCULATE(
        MIN('FACT Open Slots History'[DateOfService]), ALLSELECTED('FACT Open Slots History'[RefDate]),
        FILTER( 'FACT Open Slots History', 'FACT Open Slots History'[OpenSlots] >= 3 )
        )

	VAR vTable = 
	ADDCOLUMNS(
		_RefDate7DaysTable,
		"Next Day 3 Open", 	_NextOpenDate3Slots,
		"Days Diff", DATEDIFF( _SelectedDate, _NextOpenDate3Slots, DAY)
	)
	
    VAR _SumOfDays = 
    CALCULATE(
        SUMX(vTable, [Days Diff])
    )

    VAR _CountRows = 
    COUNTROWS(vTable)

	RETURN   
    DIVIDE(_SumOfDays, _CountRows)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[DavidKuhry · Accepted solution]

Hi, 

I was able to get it working with the below code. Someone from the reddit Power BI forum provided the answer. 

VAR _SelectedDate = 
    CALCULATE( MAX( 'FACT Open Slots History'[RefDate]) ) 

VAR _Prior7Date = _SelectedDate - 7 

VAR _RefDate7DaysTable = 
    ADDCOLUMNS( 
        SUMMARIZE( 
            CALCULATETABLE( 
                'FACT Open Slots History', 
                FILTER( 
                    ALL( 'Service Date' ), 
                    [date of service] > _Prior7Date && 
                    [date of service] <= _SelectedDate ), 
                'FACT Open Slots History'[OpenSlots] >= 3 ), 
            'Service Date'[date of service] 
        ), 
        "@EarliestDate", 
            CALCULATE( 
                MIN( 'FACT Open Slots History'[DateOfService] ) ) ) 
VAR _AvgDates = 
    AVERAGEX( _RefDate7DaysTable, 
              DATEDIFF([date of service], [@EarliestDate], DAY) ) 
RETURN _AvgDates

[lbendlin]

Read about the Windowing functions in DAX.  They can improve your code.

[DavidKuhry]

Thank you. I took a look, that does in fact look like a more elegant solution. I will see where I can take it.