Same day Last Month - Month not continuous

[Gionedis]

Hello,

I have a column with number of customers of the day, i need to compare it with the same day last month, the problem is that i don't have all days in my table, i don't have weekends and holidays, because there are no customers these days. For these days i need to find the closest day.

Ex:

day 23/03/2023 (717) i want to compare with 23/02/2023 (817)

day 22/03/2023 (773) i want to compare with 17/02/2023 (758), because I don't have 22/02/2023 so the next available day is 17/02

Thanks

[CNENFRNL]

[Jihwan_Kim · Accepted solution]

Hi,

I assume you have a date dim table in the model.

Please check the below picture and the attached pbix file. I tired to create a sample pbix file like below.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Prev month value measure: =
VAR _prevmonthdate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -1, MONTH ) )
VAR _alldatesbeforeprevmonthdate =
    ADDCOLUMNS (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _prevmonthdate ),
        "@value", [Value measure:]
    )
VAR _nonblankvaluelatestdatebeforeprevmonthdate =
    MAXX (
        FILTER ( _alldatesbeforeprevmonthdate, [@value] <> BLANK () ),
        'Calendar'[Date]
    )
RETURN
    IF (
        NOT ISBLANK ( [Value measure:] ),
        CALCULATE (
            [Value measure:],
            'Calendar'[Date] = _nonblankvaluelatestdatebeforeprevmonthdate
        )
    )

[Gionedis]

Works like a Charm Thanks, I'll study this formula to understand in the future, Thank you, my friend