on 26 Sep 2018 13:05:09
As of now I see Month on Month and Year on year calculations but at times we also need Week over Week calculation in Time Intelligence Measures? I think it will be a great value add for people who work on Weekly reports.
- Comments (22)
RE: Add Week over Week calculation in Time Intelligence Measures?
There is a need for Week on Week and Week till date measure. Quite a few organizations analyze data by weeks. +1
I have Blog how to do this currently
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
RE: Add Week over Week calculation in Time Intelligence Measures?
Please having time intelligence functionality for WEEKS is very important similar to SamePeriodLastMonth, Dateadd(,,WEEK).
RE: Add Week over Week calculation in Time Intelligence Measures?
Just remember that "week" is not the same all around the globe. The US observes it's own special standard while most of the world uses ISO 8601. Generally, calculating weeks isn't so easy, and aggregating data with added weeks numbers is something that should be considered carefully. I'd like to have a quick measure as well, but don't throw one together in 30 minutes and call it a day. Make sure it works for non-US customers, that it aggregates data correctly at year change, and that week start is configurable (since most of the world outside the US uses Monday as week start).
RE: Add Week over Week calculation in Time Intelligence Measures?
Week over Week and Week to Date DAX measure
RE: Add Week over Week calculation in Time Intelligence Measures?
The idea would be create simplified rolling time period measures to enable trended analysis.
RE: Add Week over Week calculation in Time Intelligence Measures?
A quick calculation to show the change week on week for the data
RE: Add Week over Week calculation in Time Intelligence Measures?
Week to Date (WTD) is a common industry standard for Aggregation of Sales data. Would like to see this incorporated into the list of Time Intelligence DAX Functions along with QTD, MTD, and YTD.
RE: Add Week over Week calculation in Time Intelligence Measures?
Hi All,
After waiting for ages, I realized that "every man for himself" would have worked much better than Microsoft Engineers.
So, based on some comments (herein, thanks guys) which refer to some useful web resources, the best way to get this is by creating a lean, separate, custom function to Invoke into your calendar table when adding the ISO Week Column.
Here it is:
let
ISO8601Week = (Date as date) =>
let
AncillaryWeek = (Date as date) =>
let
WeekDay = 1 + Date.DayOfWeek(Date, Day.Monday),
OrdinalDay = Date.DayOfYear(Date),
AncillaryWeekNumber = Number.RoundDown((OrdinalDay - WeekDay + 10) / 7)
in
AncillaryWeekNumber,
ThisYear = Date.Year(Date),
PriorYear = ThisYear - 1,
AncillaryNumber = AncillaryWeek(Date),
LastWeekOfPriorYear = AncillaryWeek(#date(PriorYear, 12, 28)),
LastWeekOfThisYear = AncillaryWeek(#date(ThisYear, 12, 28)),
WeekNumber = if AncillaryNumber < 1 then LastWeekOfPriorYear else
if AncillaryNumber > LastWeekOfThisYear then 1 else AncillaryNumber
in
WeekNumber
in
ISO8601Week
RE: Add Week over Week calculation in Time Intelligence Measures?
Here is solution http://datacornering.com/how-to-calculate-iso-week-number-in-power-query/
RE: Add Week over Week calculation in Time Intelligence Measures?
The implementation of the github function (https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca) may be a little daunting. Here's code to generate a DateDim with the modified gethub function:
/*
based on
M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
`weeknum(x, 21)` doesn't give the correct ISO Week-Year.
homepage:
*/
let
getISO8601Week = (someDate as date) =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Monday) //DPS - Use this to change First Day of week to Sunday: Date.DayOfWeek(d, Day.Sunday)
in
result,
getNaiveWeek = (inDate as date) =>
let
// Sunday = 1, Saturday = 7
weekday = getDayOfWeek(inDate),
weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
ordinal = Date.DayOfYear(inDate),
naiveWeek = Number.RoundDown(
(ordinal - weekday + 10) / 7
)
in
naiveWeek,
thisYear = Date.Year(someDate),
priorYear = thisYear - 1,
nwn = getNaiveWeek(someDate),
lastWeekOfPriorYear =
getNaiveWeek(#date(priorYear, 12, 28)),
// http://stackoverflow.com/a/34092382/2014893
lastWeekOfThisYear =
getNaiveWeek(#date(thisYear, 12, 28)),
weekYear =
if
nwn < 1
then priorYear
else
if nwn > lastWeekOfThisYear
then thisYear + 1
else thisYear,
weekNumber =
if nwn < 1
then lastW