cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pinkybloo Regular Visitor
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

 

Capture.PNGThe 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
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... Smiley Happy

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

 DATEDIFF - US & ISO.png

Highlighted
Dog Established Member
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
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... Smiley Happy

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

 DATEDIFF - US & ISO.png

Highlighted
Dog Established Member
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
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
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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: Datediff in weeks with Monday as a start date

@Phil_SeamarkThat's a clever approach! Smiley Happy

 

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

 

DATEDIFF - US & Mon & Weekday.png

 

Super User
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.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
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... Smiley Happy

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

Pinkybloo Regular Visitor
Regular Visitor

Re: Datediff in weeks with Monday as a start date

Oooh thank you!  This makes total sense and works!

 

xXx

Pinkybloo Regular Visitor
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