OK, here should be the final answers (I am putting all of the DAX in so this post can be marked as the solution and have all of the answers):
Pharmacies Opened in a given year
Pharmacy Opened =
CALCULATE (
COUNT ( 'pharmacy'[pharmaid] ) ,
FILTER('pharmacy',
'pharmacy'[openDate] >= FIRSTDATE ( 'Date'[Date] ) &&
'pharmacy'[openDate] <= LASTDATE ('Date'[Date])
)
)Pharmacy Closed in a given year
Pharmacy Closed =
CALCULATE (
COUNT ( 'pharmacy'[pharmaid] ) ,
FILTER('pharmacy',
'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] ) &&
'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] ) &&
'pharmacy'[closeDate] <= LASTDATE ('Date'[Date])
)
)
To calculate the number of currently active pharmacies in a given year (based on the slicer), we are going to create two measures that will denote whether or not the pharmacy was opened prior or during to the selected year(s) (by giving a value of 1) and/or closed prior to or during the selected years (giving a value of -1)
IsOpen =
IF (
CALCULATE ( MINX ( pharmacy, pharmacy[openDate] ) ) <= LASTDATE ( 'Date'[Date] ),
1,
0
)
IsClosed =
VAR minDate =
CALCULATE ( MINX ( pharmacy, pharmacy[closedate] ) )
RETURN
IF (
AND ( minDate <= LASTDATE ( 'Date'[Date] ), NOT ( ISBLANK ( minDate ) ) ),
-1,
0
)Finally, the number of current active pharmacies is simply the sum of IsOpen and IsClosed
Pharmacy Active =
CALCULATE ( SUMX ( pharmacy, [IsOpen] ) + SUMX ( pharmacy, [IsClosed] ) )
And the number of active pharmacies in the year prior to the minimum selected year
Pharmacy Active LY =
VAR minSelYear =
YEAR ( CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Year] ) ) )
VAR PAMinSel =
CALCULATE ( [Pharmacy Active], 'Date'[Year] = minSelYear - 1 )
RETURN
IF ( ISBLANK ( PAMinSel ), 0, PAMinSel )
Let me know how the rest of the report goes.
David
