Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Sean
Community Champion
Community Champion

@MarcelBeug

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

@Anonymous

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

View solution in original post

Dog
Responsive Resident
Responsive Resident

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
)

View solution in original post

9 REPLIES 9
Phil_Seamark
Microsoft Employee
Microsoft Employee

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!

@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

 

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!

@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

Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

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
Not applicable

Oooh thank you!  This makes total sense and works!

 

xXx

Sean
Community Champion
Community Champion

@MarcelBeug

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

@Anonymous

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

MarcelBeug
Community Champion
Community Champion

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)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors