cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Datediff in weeks with Monday as a start date

Hi,

I am trying to calculate how old Cases are in weeks.  I have created a column in my 'Cases' table with the following formula which I kindly pinched from @Sean

AgeingWeeks =
SWITCH (
TRUE (),
TODAY() < 'Cases'[resolveby], -1 * DATEDIFF ( TODAY(), 'Cases'[resolveby], WEEK ),
TODAY() > 'Cases'[resolveby], DATEDIFF ( 'Cases'[resolveby], TODAY(), WEEK ),
0
)

It is almost returning what I want

The formula is saying that 19/02/17 is next week and 12/02/2017.  These are Sundays and I would like 19/02/2017 to be counted as this week and 12/02/2017 to be counted as last week.

Does anyone know what the work around is for this?

Many thanks

Natalie

xXx

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Datediff in weeks with Monday as a start date

@MarcelBeug

Is there a DATEDIFF(<start>, <end>, WEEK)  equivalent in M that's ISO compliant?

@Pinkybloo

EDIT: Yes I think @Dog is onto something...

```AgeingWeeks MON =
SWITCH (
TRUE (),
TODAY () < 'Cases'[resolveby], -1 * DATEDIFF ( TODAY (), 'Cases'[resolveby] - 1, WEEK ),
TODAY () > 'Cases'[resolveby], DATEDIFF ( 'Cases'[resolveby] - 1, TODAY (), WEEK ),
0
)```

Established Member

## Re: Datediff in weeks with Monday as a start date

Hi,

I don't think that DAX has the option to pass the first day of the week option, it's a bit of a hack but you could try...

....... reducing the comparing day by one thus shifting Mondays to fall on a Sunday . You'll need to adjust the switch values as well but you get the idea.

AgeingWeeks =
SWITCH (
TRUE (),
TODAY() < 'Cases'[resolveby], -1 * DATEDIFF ( TODAY(), ('Cases'[resolveby]) -1 , WEEK ),
TODAY() > 'Cases'[resolveby], DATEDIFF ( ('Cases'[resolveby]) -1, TODAY(), WEEK ),
0
)

9 REPLIES 9
Super User

## Re: Datediff in weeks with Monday as a start date

@MarcelBeug

Is there a DATEDIFF(<start>, <end>, WEEK)  equivalent in M that's ISO compliant?

@Pinkybloo

EDIT: Yes I think @Dog is onto something...

```AgeingWeeks MON =
SWITCH (
TRUE (),
TODAY () < 'Cases'[resolveby], -1 * DATEDIFF ( TODAY (), 'Cases'[resolveby] - 1, WEEK ),
TODAY () > 'Cases'[resolveby], DATEDIFF ( 'Cases'[resolveby] - 1, TODAY (), WEEK ),
0
)```

Established Member

## Re: Datediff in weeks with Monday as a start date

Hi,

I don't think that DAX has the option to pass the first day of the week option, it's a bit of a hack but you could try...

....... reducing the comparing day by one thus shifting Mondays to fall on a Sunday . You'll need to adjust the switch values as well but you get the idea.

AgeingWeeks =
SWITCH (
TRUE (),
TODAY() < 'Cases'[resolveby], -1 * DATEDIFF ( TODAY(), ('Cases'[resolveby]) -1 , WEEK ),
TODAY() > 'Cases'[resolveby], DATEDIFF ( ('Cases'[resolveby]) -1, TODAY(), WEEK ),
0
)

Super User

## Re: Datediff in weeks with Monday as a start date

In M this would be something like:

`Number.From((Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()),Day.Monday)-Date.StartOfWeek([ResolveBy],Day.Monday))/7)`

Otherwise, ISO is more than just Monday as first day of the week; the rules are:

1. First day of the week is Monday.
2. Each week from Monday to Sunday has the same week number.
3. Week 1 of the year is the week with the first Thursday of the year.

So for example the last day of week 2016-52 is Sunday 1/1/2017.

Specializing in Power Query Formula Language (M)
Super User

## Re: Datediff in weeks with Monday as a start date

I use this approach which Mondayises the weekly aging calc

```AgeingWeeks = IFERROR(
DATEDIFF(
'Cases'[Resolvedby]- WEEKDAY('Cases'[Resolvedby],3),
NOW(),
WEEK)
,-1)```

Proud to be a Datanaut!

Super User

## Re: Datediff in weeks with Monday as a start date

@Phil_SeamarkThat's a clever approach!

However it will only show - 1 for all future weeks while the SWITCH will show as negative the number of weeks into the future!

Super User

## Re: Datediff in weeks with Monday as a start date

Yeah, I don't know why DATEDIFF throws an error when the dates are the wrong way around.  That is a bug in my opinion and many other implementations of DATEDIFF will return the correct negative number.

You can also INT rather than DATEDIFF but the DAX quickly gets ugly.

Proud to be a Datanaut!

Super User

## Re: Datediff in weeks with Monday as a start date

@Phil_Seamark

I'm guessing its sort of a safety net - to alert you - hey somewhere in your data something may be off!

I like to refer to it as the "time travel" warning - check your data - some events ended before they started!

So in that respect I can see why they did this - especially when dealing with lots of data...

And we all know time travel requires 1.21 GigaWatts and a flying delorean

Regular Visitor

## Re: Datediff in weeks with Monday as a start date

Oooh thank you!  This makes total sense and works!

xXx

Regular Visitor

## Re: Datediff in weeks with Monday as a start date

Thank you @Phil_Seamark @MarcelBeug @Dog @Sean for all your comments and suggestions.  I like it when there are lots of comments, it means I've asked a good question haha!

I've not tried the M so can't comment on that as it's not something I've started using yet.

xXx