Use first of next month in the CALCULATE filter of a measure within a certain filter context

[RobinNeven]

Dear Power BI community,

I have the following measure 'SumMonth1', which works fine (see below). Now I want the same measure, but instead of having the filter on MIN('Contract peildatum'[Contract peildatum]) I want that to be the first of next month (whatever that next month is within the current filter context).

SumMonth1 = 
    CALCULATE (
        DISTINCTCOUNT( Contracthistorie[ContractId] ),
        ALLEXCEPT (
            Contracthistorie,
            'Contract peildatum'[Contract peildatum],
            'Contract peildatum'[Contract peiljaarmaand],
            'Contract peildatum'[Is start contract peilweek],
            'Klantlabel'[Klantlabel groepcode],
            'Klantlabel'[Klantlabel code],
            'Product'[Product groepcode]
        ),
        'Contract peildatum'[Contract peildatum] = MIN('Contract peildatum'[Contract peildatum])
    )

The goal is to get the difference between the two totals in below picture (blanked out), whatever the selection is in the slicer (assuming always two selections):

So the left total I solved, that's what measure 'SumMonth1' is for. Now I just need to know how to dynamically calculate the total on the right, so that I can subtract the two.

The following for the total on the right (SumMonth2) doesn't work by the way, because the MAX('Contract peildatum'[Contract peildatum]) is 9/30/2021 and not 9/1/2021:

SumMonth2 = 
    CALCULATE (
        DISTINCTCOUNT( Contracthistorie[ContractId] ),
        ALLEXCEPT (
            Contracthistorie,
            'Contract peildatum'[Contract peildatum],
            'Contract peildatum'[Contract peiljaarmaand],
            'Contract peildatum'[Is start contract peilweek],
            'Klantlabel'[Klantlabel groepcode],
            'Klantlabel'[Klantlabel code],
            'Product'[Product groepcode]
        ),
        'Contract peildatum'[Contract peildatum] = MAX('Contract peildatum'[Contract peildatum])
    )

A lot of DAX statements do not work in the filter of a CALCULATE measure I found, like DATEADD, which makes it quite hard. I think it's a matter of just knowing the right DAX statement, but I cannot figure it out. Thanks a lot in advance for any help!

[amitchandak · Accepted solution]

@RobinNeven , if you want first date on next month

SumMonth1 = 
    CALCULATE (
        DISTINCTCOUNT( Contracthistorie[ContractId] ),
        ALLEXCEPT (
            Contracthistorie,
            'Contract peildatum'[Contract peildatum],
            'Contract peildatum'[Contract peiljaarmaand],
            'Contract peildatum'[Is start contract peilweek],
            'Klantlabel'[Klantlabel groepcode],
            'Klantlabel'[Klantlabel code],
            'Product'[Product groepcode]
        ),
        'Contract peildatum'[Contract peildatum] = eomonth(MIN('Contract peildatum'[Contract peildatum]),0)+1
    )

[RobinNeven]

Of course, adding a day to the last day of the current month works fine for this, silly I didn't think of it. But thanks! 🙂