I have a table of accounts similar to this:
| Account ID | Date Opened | Date Closed | Status | ProductTypeID |
| 1500311D | 28/07/2015 | 16/01/2019 | 3 | C |
| 716032 | 22/06/2017 | 25/02/2019 | 3 | C |
| Z1000 | 1/04/2008 | 31/12/2100 | 1 | C |
I'm attempting to calculate a graph of open accounts over time. DimDate is my calendar table, with all dates from 1/1/2016 to now. My slicer for DimDate is on the page. My formula for number of open accounts is:
# Loans Open =
CALCULATE(
DISTINCTCOUNT(DimAccount[AccountID]),
FILTER(
DimAccount,
DimAccount[DateOpened] <= LASTDATE(DimDate[Date])
&& OR(DimAccount[DateClosed] >= LASTDATE(DimDate[Date]), DimAccount[status] = 1)
&& DimAccount[ProductTypeId] <> "D"
)
) In this example my measure would return 2 with the date slicer set to 4 April 2018. The account "Z1000" is not recognised even though it is open on 4 April 2018. Any clues?
The date table (DimDate) has no direct relationship to the DimAccount table either through closing or opening date. There are some indirect relationships e.g Each account has a number of AccountLogs, each AccountLog has a Date which is related to the DimDate table.
