DISTINCTCOUNT records for the previous month from today's date

[D_PBI]

Hi,
I need to count unqiue values from an ID (leadid) column where that ID's datestamp (Date of First Contact) is within the previous month to today's date.
Below is an attempt to acheive this, although I have been trying a few differents ways all without success.

__distinctNumberOfLeadID_All__ForPreviousMonth =

VAR __monthStart = STARTOFMONTH( DATEADD( TODAY(), -1, MONTH ) )

VAR __monthEnd = ENDOFMONTH( DATEADD( TODAY(), -1, MONTH ) )

VAR _result =

    CALCULATE(

        DISTINCTCOUNT( Disclosures[leadid] ) + 0,

        FILTER(

            ALLSELECTED( Disclosures ),

            Disclosures[Date of first contact] >= __monthStart  &&

            Disclosures[Date of first contact] <= __monthEnd &&

            NOT( ISBLANK( Disclosures[Date of first contact] ) )

        )

    )

RETURN

    _result

It's erroring on the inclusion of the TODAY() function.
Let's say the today's date is 16/05/2024 then I would like to count all unique IDs that have a datestamp falling within the period 01/04/2024 - 30/04/2024, or if today's date was the 02/01/2024 then I would like to count all unique IDs that have a datestamp falling within the period 01/12/2023 - 31/12/2023.

I should add there is a Date dimension table in play but I have set the 'interaction' on the date slicer so it doesn't affect the visual this wanted measure is applied to. With not using any fields from the date dimension table in this measure, I assume my measure attempt should work.

Thanks. 

[TomMartens · Accepted solution]

Hey @D_PBI ,

you can not use DATEADD, if the first parameter is not referencing a date/datetime column, for this reason you have to determine the first and last day of the previous month differently, see the measure below:

Measure 2 = 
var startOfPrevMonth = EOMONTH( TODAY() , -2 ) + 1
var endOfPrevMonth = EOMONTH( TODAY() , -1)

VAR _result =
    CALCULATE(
        DISTINCTCOUNT( Disclosures[leadid] ) + 0,
        
        CALCULATETABLE(
            ALLSELECTED( Disclosures )
        , DATESBETWEEN( Disclosures[Date of first contact] , startOfPrevMonth , endOfPrevMonth )
        )
    )

RETURN
    _result

I'm not sure about the condition
NOT( ISBLANK( Disclosures[Date of first contact] ) )
Maybe the filter condition of datesbetween is all you need.

Nevertheless, instead of DATEADD use EOMONTH in combination with  TODAY.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[D_PBI]

@TomMartens - thank you for the quick response. It works as I need.