Building on PowerPivotPro's "Active Promotions pattern" I have tables of accounts which have:
- An opening Date
- A closing Date
- A status (1 being open right now.) Once an account is closed the closing date is filled (otherwise it's empty), and the status is changed)
I am looking to create a graph showing the number of accounts open each day. I have a date hierarchy on my date column. Putting this on my X axis with only "Month" selected seems to create the roughly correct figures but if I add "Date" and "Month" to the date hierarchy (so each data point represents a day) the result is too small, like it is only counting accounts which are open in the present day and were open then (in the past context).
Here is my current measure:
maxDate = MAX(DimDate[Date])
# Loans Open =
VAR EndPeriod = [maxDate]
Return
CALCULATE(
DISTINCTCOUNT(DimAccount[Pk]),
FILTER(
DimAccount,
DimAccount[DateOpened] <= EndPeriod
&& OR(DimAccount[DateClosed] >= EndPeriod, DimAccount[status] = 1)
&& DimAccount[ProductTypeId] <> "D"
)
)
