DATEDIFF no option for WEEK interval starting on MONDAY

[ggyczew]

As mentioned in topic there is no option for setting value to treat Monday as first day of week in DATEDIFF formula. As a result We get wrong DIFF for week range dates. There should be option for global setting.

[v-yanjiang-msft]

Hi @ggyczew ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[ggyczew]

Not elegant solution for shifting result values

"OffsetWeek", DATEDIFF(TODAY(), [Date], WEEK) - IF(WEEKDAY([Date],2)<7,0,1)

[v-yanjiang-msft · Accepted solution]

Hi @ggyczew ,

This seems by design and can't be changed in the function, you can only get the expected result by the formula.

This also works:

"OffsetWeek", DATEDIFF(TODAY(), [Date]-1, WEEK)

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ggyczew]

If there is no such functionality in the DATEDIFF function, solution should be at least described in the documentation which also is translated with major errors in Polish version. 

Function documentation suggests that one can use intervals names like DZIEŃ, TYDZIEŃ, MIESIĄC.

This is misleading.

[v-yanjiang-msft]

Hi @ggyczew ,

Sorry for the bad experience, do you mean this part in the document.

Best Regards,
Community Support Team _ kalyj

[ggyczew]

Usage for Offset is in relative selection last X weeks, X Months etc. It is simple and works perfectly

[ggyczew]

In my country Poland week starts on Monday. There is setting in the WEEKDAY but there is no such in the  DATEDIFF with WEEK interval.

There are two option

1) global setting which would provide context for such functions.

2) localized paramaters which would translate to proper function result. On the translated version DATEDIFF, funkcja (DAX) - DAX | Microsoft Docs Microsoft suggests that Polish users can use as parameters localized text like TYDZIEŃ (=WEEK). This does not work.

EVALUATE
ADDCOLUMNS(
    CALENDAR(DATE (2022, 5, 1), DATE (2022, 5, 22)),
    "WeekNum", WEEKNUM([Date],2),
    "DayOfWeek", WEEKDAY([Date],2),
    "OffsetWeek", DATEDIFF(TODAY(), [Date], WEEK)
)

[selimovd]

Hey @ggyczew ,

DATEDIFF returns the numbers of hours/days/weeks, etc. between two dates.

I don't understand where exaclty you want to use Monday now?

Best regards

Denis