cancel
Showing results 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

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

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

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

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
)

9 REPLIES 9
Microsoft Employee

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!

Community Champion

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

Microsoft Employee

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!

Community Champion

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

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

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

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

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors