Include first date of month in measure which compares prices current month with previous month

[Anonymous]

Dear All,

I am seeking for some support for the following:

I have the following measure, comparing prices of current month wit previous month, by making use of a slicer for the date. In this example prices of current month (December) are compared with previous month (November). Within the measure it's making use of the date as maintained in a date table ('Dim Datum'). Within the slicer I selected Thursday 1st December 2022. The filed name of the table is marked in red below. 

Measure 1:

Delta price prev month =

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(__CurrentYear,__CurrentMonth,1)

VAR __Date2 = DATEADD('Dim Datum'[Date Actual], -1, MONTH)

RETURN

CALCULATE (

    [Historic price],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = __Date1

    )

)

    - CALCULATE (

        [Historic price],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = __Date2

        )

    )

Slicer selection: 

Now I want to get rid of the slicer and want to show with a card with the first date of the month. I created the following measure (2):

First of month = DATE(YEAR(today()),month(today()),1)

Resulting in the following card: 

When I replace within measure 1 the field name 'Dim Datum'[Date Actual] by measure 2 (First of month), it does give an error, saying it cannot be found or cannot be used in this expression. 

Any suggestion to get this solved is welcome.

Thanks!

[Barthel · Accepted solution]

Hey @Anonymous,

It is not allowed to use a variable as the first argument to a DATEADD function. This must always be a reference to a date column. Instead of the DATEADD function you could use the EOMONTH, which does allow working with variables. The function's first argument is the start date ('First of month'). Then you indicate how many months you want to go back or forward. In this case, we go back two months, so that we get the end of the month two months ago (October 31, 2022). To which we then add 1 so that we get the first date of the previous month (November 1, 2022).

Instead of the FILTER ALL functions in the CALCULATE, I recommend working with REMOVEFITLERS.

Delta price prev month =
VAR _Date1 = [First of month]
VAR _Date2 =
    EOMONTH ( _Date1, -2 ) + 1
RETURN
    CALCULATE (
        [Historic price],
        REMOVEFILTERS ( 'Dim Datum' ),
        'Dim Datum'[Date Actual] = __Date1
    )
        - CALCULATE (
            [Historic price],
            REMOVEFILTERS ( 'Dim Datum' ),
            'Dim Datum'[Date Actual] = __Date2
        )

[Anonymous]

Thanks! This works perfectly!