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.   ...
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.

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.