Forum Discussion

paulsnet1986's avatar
paulsnet1986
Helper I
1 year ago
Solved

Historical Record Counts Using Date Table Help

I have a table with records that have a Start_Date and End_Date. They are connected to a master date table with an active connection to the Start_Date and an inactive connection to the End_Date.

 

I'm trying to create a matrix with yyyyMM in the columns and DISTINCTCOUNT of the records, where the record is counted in a given month if the Start_Date is less than or equal to the last day of a given month and the End_Date is greater than or equal to the first day of a given month.

 

I tried the following formula and got a blank result. Any help would be appreciated.

 

_Functional Location Net =
CALCULATE(DISTINCTCOUNT('Functional Location Master'[Functional Location]),
'Functional Location Master'[Functional Startup Date] <= SELECTEDVALUE('Date'[Date]),
'Functional Location Master'[Functional End Date Adjusted] >= SELECTEDVALUE('Date'[Date]))

 

 

 

 

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    Try

    _Functional Location Net =
VAR StartMonth =
    MIN ( 'Date'[Date] )
VAR EndMonth =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( 'Functional Location Master'[Functional Location] ),
        'Functional Location Master'[Functional Startup Date] <= EndMonth,
        'Functional Location Master'[Functional End Date Adjusted] >= StartMonth,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

    This will return any records where the start date is before the end of the month, including in prior months. If you want to exclude prior months then you can add additional logic.

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    Try

    _Functional Location Net =
VAR StartMonth =
    MIN ( 'Date'[Date] )
VAR EndMonth =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( 'Functional Location Master'[Functional Location] ),
        'Functional Location Master'[Functional Startup Date] <= EndMonth,
        'Functional Location Master'[Functional End Date Adjusted] >= StartMonth,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

    This will return any records where the start date is before the end of the month, including in prior months. If you want to exclude prior months then you can add additional logic.