Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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 )
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
)
I use this approach which Mondayises the weekly aging calc
AgeingWeeks = IFERROR( DATEDIFF( 'Cases'[Resolvedby]- WEEKDAY('Cases'[Resolvedby],3), NOW(), WEEK) ,-1)
@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!
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.
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
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
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
)
Oooh thank you! This makes total sense and works!
xXx
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 )
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
121 | |
73 | |
71 | |
63 |