DAX weekend day calculation doesn't work for me

[Anonymous]

I am not able to have a simple dateadd for weekend day calculation work.   New to Power BI.  Can someone help?

The event date is pretty much simple daily date. and The weending2 is the calculated date based on event date.    Why the simple calculation won't work?

WeekEnding2 = FORMAT(DATEADD(datalake[last_event_date], (7 - (Weekday(datalake[last_event_date],2))), DAY), "YYYY-MM-DD")

Issue Screen shot

[Vvelarde · Accepted solution]

hi @Anonymous

try this:

Weekending = datalake[last_event__date]+(7-WEEKDAY(datalake[last_event__date];2))

[v-sihou-msft · Accepted solution]

@Anonymous

I can’t find any no syntax issue in your formula. But there is some limitations of DATEADD. The second one below should be the reason why your calculation won’t work for some dates.

1. DATEADD only works with a contiguous date selection and raises an error otherwise.
2. DATEADD only returns days existing in the date column received as first argument.
3. When a corresponding day does not exist in the corresponding month after the shift operation, the result of DATEADD includes the last day of the corresponding month.
4. When the selection includes the last day of a month, and the selection is more than one day, then the result of DATEADD includes all the days between the corresponding day in the shifted month and the end of the shifted month.

However you can get the expected results by creating a calculated column with following formula.

WeekEnding2 = 
FORMAT (
    datalake[last_event_date]
        + ( 7 - WEEKDAY ( datalake[last_event_date], 2 ) ),
    "YYYY-MM-DD"
)

Regards,

[Anonymous]

Thanks @v-sihou-msft

[Vvelarde · Accepted solution]

hi @Anonymous

try this:

Weekending = datalake[last_event__date]+(7-WEEKDAY(datalake[last_event__date];2))