Datediff in weeks with Monday as a start date

[Anonymous]

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

[Sean · Accepted solution]

@MarcelBeug

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

@Anonymous

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
)

 

[Phil_Seamark]

I use this approach which Mondayises the weekly aging calc

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

[Sean]

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

[Phil_Seamark]

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.  

[Sean]

@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

[Anonymous]

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

[Dog · Accepted solution]

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
)

[Anonymous]

Oooh thank you!  This makes total sense and works!

xXx

[Sean · Accepted solution]

@MarcelBeug

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

@Anonymous

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
)

 

[MarcelBeug]

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.