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!

[Valpo617 · Accepted solution]

@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
            )
        )
)

[techies · Accepted solution]

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

)

[V-yubandi-msft]

Hi @HAM ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @HAM ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[V-yubandi-msft]

Hi @HAM ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[divyed · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

@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