DateAdd ( -1, Month ) skipping days in January and adding non-existent days in February

[HAM]

Hello everyone!

I am trying to compare days' sales with the same day previous month in a column chart. Here are the measures I am using on the y-axis of a clustered column chart:
Current Month = CALCULATE( SUM('2023'[AMOUNT]), Calandar[Year] = YEAR(TODAY()) )

NetSalesPrevMonth = CALCULATE(SUM('2023'[AMOUNT]), DATEADD('Calandar'[Date], -1, MONTH))
When I select the current month as March I get the following chart:

The problem being I am getting columns for Feb 29, 30 and 31 (which have the same value as Feb 28, repeated). I would like to get blanks for these days as these dates don't exist.

And when I select the current month as February, I get the following chart:

The problem now is that I am only getting values up to Jan 28 and no values for Jan 29, 30 and 31.
How can I fix this?

Thank you for reading!

[divyed]

Hello @HAM ,

This is bit tricky and you need to modify your dax to get rid of DATEADD. Here is an updated dax you can use

PreviousMonthSales1 =

VAR CurrentDate = SELECTEDVALUE(Datestbl[Date])

VAR PrevMonthDate = EDATE(CurrentDate, -1)

-- Get the day of the month for the previous date

VAR PrevMonthDay = DAY(CurrentDate)

-- Find the actual matching date in the previous month

VAR ActualPrevMonthDate =

    CALCULATE(

        MAX(Datestbl[Date]),

        Datestbl[Year] = YEAR(PrevMonthDate),

        MONTH(Datestbl[Date]) = MONTH(PrevMonthDate),

        DAY(Datestbl[Date]) = PrevMonthDay

    )

RETURN

IF(

    NOT(ISBLANK(ActualPrevMonthDate)),

    CALCULATE(

        [TotalSales],

        Datestbl[Date] = ActualPrevMonthDate

    ),

    BLANK()

)

I have selected MARCH from slicer and here is the result. I don't have sales data for 29 and 30 March in my dataset hence empty here.

Case 2 : when you select FEB from slicer, it will show data upto 28th Fen only as this is the filter you are supplying from slicer.

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Warm Regards,

Neeraj

[techies]

Hi @HAM please check this

Current month =

    SUMX(

        WINDOW(

            0, REL,  

            0, REL,

            ALLSELECTED('date'[Year], 'date'[month]),

            PARTITIONBY('date'[Year])

        ),

        [Sales]

    )

NetSalesPrevMonth =

VAR CurrentMonth = SELECTEDVALUE('date'[month])

VAR CurrentYear = SELECTEDVALUE('date'[year])

VAR PrevMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)

VAR PrevYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)

RETURN

CALCULATE(

    [Sales],

    ALLSELECTED('date'),

    'date'[month] = PrevMonth,

    'date'[year] = PrevYear

)

[Valpo617]

@HAMInteresting if that is the case you could just switch away from DATEADD to avoid that final date mismatch and use another function like EOMONTH to calculate your date range. Based upon the information you provided I made an updated dax using EOMONTH, but without looking at some sample data I can't fully test if it will work on your solution.

NetSalesPrevMonth = 
CALCULATE(
    SUM('2023'[AMOUNT]),
    VAR CurrentDate = MAX('Calandar'[Date])
    VAR LastDayPrevMonth = EOMONTH(CurrentDate, -1)
    VAR FirstDayPrevMonth = EOMONTH(CurrentDate, -2) + 1
    RETURN
        IF(
            CurrentDate <= DAY(LastDayPrevMonth),
            FILTER(
                ALL('Calandar'[Date]),
                'Calandar'[Date] >= FirstDayPrevMonth &&
                'Calandar'[Date] <= LastDayPrevMonth
            )
        )
)

[Valpo617]

Hello Ham,

From what I see you are missing the calendar context in the NetSalesPrevMonth measure to get all the previous month days. Your current implementation is mainly context based upon what the current month days are. Adding the ALL filter to your measure on the calendar table should fix your issue. I provided the updated DAX below.

CALCULATE(
SUM('2023'[AMOUNT]),
DATEADD('Calendar'[Date], -1, MONTH),
ALL('Calendar')
)

The reasoning this is occuring is due to DATEADD using month shifting the date back exactly one month. Which can cause issues with months with different numbers. When going from February to March this will cause DATEADD to try and map each date in March to February's date, but the last 3 days aren't available so it will map to the last available day which is the 28th. January to February is the oppisite since since their are less days now it will not map those last few days with your context.

[HAM]

Hello @Valpo617 , adding All('Calendar') returns exactly the same results