Hi Bas, great video as always! Weeks are a really pain the *ss. What people also should be careful about is that every 7 years the number of weeks in a year is different. When calculating the 'same week previous year' you' ll have to deal with this as well. Then another issue is that because of that, for example, Christmas doesn't always fall in the same weeknumber. So another complexity to deal with! Good to read from everyone's reactions I'm not the only one fiddling with weeks 😊
@@marounsader318 Total Sales LY = VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1 VAR Result = CALCULATE( [Total Sales], dimDate[Year_ISO] = LastYear ) RETURN Result
This video gave me the solution to a client requirement for reporting week over week calculations! They needed the previous year included, but did not want to show the year in the visual and the usual filters were filtering out the data. Thank you!
Wow did not know the WEEKNUM iso formatting "21". Thanks! Keep it up. You are one of the few PBI youtubers that actually present valuable content. Keep it up!
Paused the video in the middle, to say you that you are a great instructor. I was always stuck at these week based calculations, and was searching for some articles or videos that could help me understand it better. This is the video I was looking for. Thank you so much, keep posting such great contents, wish you health and happiness. 😊
The 'Don't Summarize' option for certain columns is amazing! Until now, I kept using it in all the tables I inserted in the reports, over and over. Bas, you rule.
This is extremely useful!! I've been struggling for months to get a running previous week calculation! But could you PLEASE make a full video on the ultimate calendar table which includes the week number for several years, for example, from 2020 until 2025?
I’ve been struggling so much with calculations per week. If possible always try to avoid. This helps a lot in understanding what is going on and not go crazy.
Thanks for this. How do we take it further by showing Week 1 to Week 4 or Week 5 for each month. Every time we want to see Week 1 to Week 4 or Week 5 of each month. That way for example we're able to compare Week 3 of previous month with that of current month.
Great videos! Could you help me in solving the next problem? Instead of scrolling through a long table of data or using a filter selection pane of 52 weeks, I would like to use 2 buttons in the form of arrows in order to skip to next or previous week(s). Is this even possible in Power BI?
Hi Bas - good to know (tricky) - I have 3 small questions for you : 1) Instead of building continuous weeks, can we use OFFSET function to have the previous sales ? (as this is on the previous line or will we have issue when go over a new year ?) ===> Happy New Year to you by the way 🎄 2) For Europe, maybe the video is missing the last info "(INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/ --> what do we have to write after / 3) Why '3' in the formula for Europe ? Thank you in advance - Geneviève
Thanks for the video! Is there a way to incorporate week into the standard date hierarchy? Your solution is good for one measure, but what if I have a ton of them? Do i need to create a second measure for each of them just to be able to track it per week?
Thanks for this. How can I avoid the error in the US standard week format when transiting between years without resorting to ISO cos my week is Sunday to Saturday?
Great Video!! It would be really great if you mentioned how to sort the ISO week in column or line chart so Week 52 appears at the beginning and not the end. Thanks.
I'm confused on how he setup the variance. I can't get it to properly correlate the Min/Max Year. Also, I assume DateKey is something Bas setup separately?
Hi Bas, Im following your video but I seem to be having an issue when completing this For "(INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/ When using "/" I receive an error. Is there supposed to be more after that's missing in the video? I appreciate any help! thanks. And love the videos! Very informative :)
Hi Baz, I have been going over this video again but I am still having a problem with dates. I have visuals that are filtered by month slicer. However, if a week spans two months, it doesn't show. Any idea how I solve this, please?
Difficult to say where you are going wrong. I would double check the week column. Does it return the last date of the week (which would result in your problem) or do you have it like w1, w2, … for all the dates that fall in the respective weeks
On the axis I need to represent weekly volume. And the representation should be like 2021-Jan-Week1, 2021-Jan-Week2 etc. Everything works fine but if there are days in a week falls in two months then on the axis it shows like 2021-Jan-Week6, 2021-Feb-Week6. How can I add those days in previous or later week. And I have requirement to show same week in the Last year and show WoW%. But numbers are not showing properly when there is a week falls in two months. Could you please help me with this
to calculate the same week last year -> Total Sales LY = VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1 VAR Result = CALCULATE( [Total Sales], dimDate[Year_ISO] = LastYear ) RETURN Result to add days in prev or later week you need to implement a 445 / 454 calendar in your date table
Great video. However for a calendar week ( starting on a Sunday or Monday) I believe you can have 54 weeks in some years so the sequence calculation will not work in all instances?
I am not able to figure out why, in some cases, when I calculate prior week as we have done here (weekID -1) the value returned is blank. Despite the fact that there is continuous data with no gaps.
I run into similar issues when trying to calculate the same week prior year (week iD - 53), in which I have many blanks returned, yet where there is data, it is correctly locating the proper week in the prior year
Bit tricky to say what it could be. First check if it is correct for the weeks where it does show. And then see which weeks have blanks. How r they different from the weeks where u do have a value. What should the value be? .. diving deeper like this usually makes me find the problem.
Nice trick as always!! Thanks I would like to know the process to create such elegant UA-cam videos Additionally, I wanted to know is there a way to create Power BI like in built slicers to connect with bookmarks in order to create web page like GUI in PBI
Thank you so much Rajnish! That's really nice to hear. I will think about creating a behind the scenes video. I think what you are looking for is the bookmark navigator - you find it under buttons 🙂
@@HowtoPowerBI Thanks Bas for quick reply. I have created a button like slicer with dropdown arrow and a popup window to show slicer like effect but I needed to know if something better can be achieved. Unfortunately due to privacy I can't share the report link which I am developing. Used a lot of tricks from your UA-cam videos.
Hey Bas, have you hacked my laptop? This is the topic a customer asked me 3 hours ago and since then i've been struggling to find a solution. And then magically your video notification appeared. And it totally saved the day!!! Thanks so much, now i get the chance to look like a power bi guru 😉...
Hey, thank you for all the Nice content ! I have an issue with the dates ... how can i remove a date hierarchy. I found on google how to remove it on the whole file but this is not what i want :'(
thank you Angélique! My guess is that you are using the auto date/time intelligence, which creates a hidden date table for you with the hierarchy (that you can't delete). So you would need to turn auto date/time off and build a custom date table and then you have more control over the date table. For more details see this video -> ua-cam.com/video/MhC4zj2byBQ/v-deo.html
@@HowtoPowerBI thank you very much for your quick answer ! This morning i worked on a brand new dashboard using a lot of new things thanks to your vidéo, i really love them, i hope you will continue à very long time to do this ^^ I wish you à very good night, thx Again for everything
Thank you BAS,great video as always. Can you show how to get a value of a week in comparison with diferente years? for example show in week 1 of 2022 the value of week 1 of 2021.
I think this will work :) Total Sales LY = VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1 VAR Result = CALCULATE( [Total Sales], dimDate[Year_ISO] = LastYear ) RETURN Result
that topic deserves a video on its own 🙂 however it comes down to adding columns for the fiscal year and week to your date table - similar logic as in this video
You haven't notice that leap years starting on saturdays have 54 weeks... 1+52*7+1 = 366. Won't a little correction every 28 years being needed, being the next one on 2028?
Hi Bas, great video as always! Weeks are a really pain the *ss. What people also should be careful about is that every 7 years the number of weeks in a year is different. When calculating the 'same week previous year' you' ll have to deal with this as well. Then another issue is that because of that, for example, Christmas doesn't always fall in the same weeknumber. So another complexity to deal with! Good to read from everyone's reactions I'm not the only one fiddling with weeks 😊
can you share please same week last year measure or approach ?
@@marounsader318 Total Sales LY =
VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1
VAR Result =
CALCULATE(
[Total Sales],
dimDate[Year_ISO] = LastYear
)
RETURN
Result
@@HowtoPowerBI thank you so much 🙏
This video gave me the solution to a client requirement for reporting week over week calculations! They needed the previous year included, but did not want to show the year in the visual and the usual filters were filtering out the data. Thank you!
Wow did not know the WEEKNUM iso formatting "21". Thanks! Keep it up. You are one of the few PBI youtubers that actually present valuable content. Keep it up!
And thanks for giving the formula for ISO_Year! Great content!
Paused the video in the middle, to say you that you are a great instructor.
I was always stuck at these week based calculations, and was searching for some articles or videos that could help me understand it better. This is the video I was looking for.
Thank you so much, keep posting such great contents, wish you health and happiness. 😊
The 'Don't Summarize' option for certain columns is amazing! Until now, I kept using it in all the tables I inserted in the reports, over and over. Bas, you rule.
This is extremely useful!! I've been struggling for months to get a running previous week calculation! But could you PLEASE make a full video on the ultimate calendar table which includes the week number for several years, for example, from 2020 until 2025?
3 hours ago I was looking just for this. Now it is there, you literally made my day. Thank you very much and keep up the good work👍👋🏻
haha in just in time 😅happy I could help! 😊
I’ve been struggling so much with calculations per week. If possible always try to avoid. This helps a lot in understanding what is going on and not go crazy.
Thanks for this. How do we take it further by showing Week 1 to Week 4 or Week 5 for each month. Every time we want to see Week 1 to Week 4 or Week 5 of each month. That way for example we're able to compare Week 3 of previous month with that of current month.
Great videos! Could you help me in solving the next problem? Instead of scrolling through a long table of data or using a filter selection pane of 52 weeks, I would like to use 2 buttons in the form of arrows in order to skip to next or previous week(s). Is this even possible in Power BI?
Amazing - whenever I have a question pop into my head, you have a video to answer it. Great content as always!
Happy to have the perfect timing! Hopefully next time the same :)
Cool trick to create a week ID that continually rises, also from year to year!
Hi Bas - good to know (tricky) - I have 3 small questions for you :
1) Instead of building continuous weeks, can we use OFFSET function to have the previous sales ?
(as this is on the previous line or will we have issue when go over a new year ?)
===> Happy New Year to you by the way 🎄
2) For Europe, maybe the video is missing the last info "(INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/
--> what do we have to write after /
3) Why '3' in the formula for Europe ?
Thank you in advance - Geneviève
i was searching for this and here you are, thank you BAS
awesome! happy it helped Santhosh
so thankful for your videos! I'm glad that i found this channel. Where can i find the tutorial for the 4-4-5 calendar please?
Thanks for the video!
Is there a way to incorporate week into the standard date hierarchy? Your solution is good for one measure, but what if I have a ton of them? Do i need to create a second measure for each of them just to be able to track it per week?
just one question though - what if I need to be able to drill down correctly between ISO year and the true date year? is there a solution for this?
Great video. Thanks! Can you tell me, what should I change for the calculation if my week is Sunday to Saturday?
3:57 in the video ->
return_type Week begins
1 or omitted Sunday
2 Monday
11 Monday
12 Tuesday
13 Wednesday
14 Thursday
15 Friday
16 Saturday
17 Sunday
21 Monday
Thanks for this. How can I avoid the error in the US standard week format when transiting between years without resorting to ISO cos my week is Sunday to Saturday?
can we go back wards i.e. from week no to week start date or week end date
Great Video!! It would be really great if you mentioned how to sort the ISO week in column or line chart so Week 52 appears at the beginning and not the end. Thanks.
I'm confused on how he setup the variance. I can't get it to properly correlate the Min/Max Year. Also, I assume DateKey is something Bas setup separately?
Thanks a lot Bas , I did this for month too now I want to achives same thing for days please help me in this regard.
One Question, DAX to get week number starting from Saturday to Friday.
In European format using weekid_iso or -7 days doesn’t give the exact same results? Am I missing something?
Hi Bas, Im following your video but I seem to be having an issue when completing this For "(INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/ When using "/" I receive an error. Is there supposed to be more after that's missing in the video? I appreciate any help! thanks. And love the videos! Very informative :)
try ((INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/7)
Watching all your videos and trying in my system. Thank you😄 !!
awesome Punith, thanks! 😊😎👊
Hi Baz, I have been going over this video again but I am still having a problem with dates. I have visuals that are filtered by month slicer. However, if a week spans two months, it doesn't show. Any idea how I solve this, please?
Difficult to say where you are going wrong. I would double check the week column. Does it return the last date of the week (which would result in your problem) or do you have it like w1, w2, … for all the dates that fall in the respective weeks
Nicely done, thanks for this!
When I write 21 as the second argument to weeknum , it gives error as unrecognised parameter
great tutorial. thanks
Thank Bas for this great topic. Now, in addition to a repository for calendar elements and fiscal elements, there will be a repository for ISO Items
Is there a way to sort multiple dates in order on a chart. Say month, week, day. So that I can show a day in calendar order?
yes, you can use the sort by column feature -> see explanation here ua-cam.com/video/T6p5xy35UPE/v-deo.html
Very, very USEFUL. A great big thank you !
i hoped you would have a solution for this one in M (power query)
On the axis I need to represent weekly volume. And the representation should be like 2021-Jan-Week1, 2021-Jan-Week2 etc. Everything works fine but if there are days in a week falls in two months then on the axis it shows like 2021-Jan-Week6, 2021-Feb-Week6. How can I add those days in previous or later week. And I have requirement to show same week in the Last year and show WoW%. But numbers are not showing properly when there is a week falls in two months. Could you please help me with this
to calculate the same week last year ->
Total Sales LY =
VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1
VAR Result =
CALCULATE(
[Total Sales],
dimDate[Year_ISO] = LastYear
)
RETURN
Result
to add days in prev or later week you need to implement a 445 / 454 calendar in your date table
Superb superb superb god bless u always
thank you so much Laiq! 😊
Great video! Could you use TOTALYTD with this date table? Thanks in advance!
I hate working with weeks. This will be a nice reference video - thanks
Great video. However for a calendar week ( starting on a Sunday or Monday) I believe you can have 54 weeks in some years so the sequence calculation will not work in all instances?
oh indeed 😅😄 happened 5 times since 1900 (1916, 1944, 1972, 2000, 2028, 2056)
here is my adjusted version "WeekID", WEEKNUM([Date], 1) + (YEAR([Date]) - _MinYear) * (53 + INT(MOD(YEAR([Date])-1915,28)=1)),
I am not able to figure out why, in some cases, when I calculate prior week as we have done here (weekID -1) the value returned is blank. Despite the fact that there is continuous data with no gaps.
I run into similar issues when trying to calculate the same week prior year (week iD - 53), in which I have many blanks returned, yet where there is data, it is correctly locating the proper week in the prior year
Bit tricky to say what it could be. First check if it is correct for the weeks where it does show. And then see which weeks have blanks. How r they different from the weeks where u do have a value. What should the value be? .. diving deeper like this usually makes me find the problem.
Nice trick as always!! Thanks
I would like to know the process to create such elegant UA-cam videos
Additionally, I wanted to know is there a way to create Power BI like in built slicers to connect with bookmarks in order to create web page like GUI in PBI
Thank you so much Rajnish! That's really nice to hear. I will think about creating a behind the scenes video. I think what you are looking for is the bookmark navigator - you find it under buttons 🙂
@@HowtoPowerBI Thanks Bas for quick reply.
I have created a button like slicer with dropdown arrow and a popup window to show slicer like effect but I needed to know if something better can be achieved. Unfortunately due to privacy I can't share the report link which I am developing. Used a lot of tricks from your UA-cam videos.
Great vid! Great channel!
Thank you Bas, Its really amazing. Thank you for sharing!
0:00 - 4:50
Would also be very interested in 2 week scenarios, would that be just adjusting the increasing week count with 2 weeks instead of 1?
yes indeed 🙂 just replace "-1" with "-2" and that's it
@@HowtoPowerBI When the year changes, and we are doing -1 for previous week; are we including all the 7 days in the previous week?
Bas, how do you sort the table by multiple columns?
Hold shift and click on the column headers
Hey Bas, have you hacked my laptop?
This is the topic a customer asked me 3 hours ago and since then i've been struggling to find a solution. And then magically your video notification appeared. And it totally saved the day!!!
Thanks so much, now i get the chance to look like a power bi guru 😉...
Awesome :) this is magic, no?
@@HowtoPowerBI This is totally "Houdini and Copperfield together" stuff...
Great stuff by you.... 😊.
great video but i need to replay it multiple times because im not a fluent english speaker. thanks btw
thanks, hope the auto translation subtitles help a bit
Hey, thank you for all the Nice content !
I have an issue with the dates ... how can i remove a date hierarchy. I found on google how to remove it on the whole file but this is not what i want :'(
thank you Angélique! My guess is that you are using the auto date/time intelligence, which creates a hidden date table for you with the hierarchy (that you can't delete). So you would need to turn auto date/time off and build a custom date table and then you have more control over the date table. For more details see this video -> ua-cam.com/video/MhC4zj2byBQ/v-deo.html
@@HowtoPowerBI thank you very much for your quick answer !
This morning i worked on a brand new dashboard using a lot of new things thanks to your vidéo, i really love them, i hope you will continue à very long time to do this ^^
I wish you à very good night, thx Again for everything
Heel nice Bas, dank!
Thank you BAS,great video as always. Can you show how to get a value of a week in comparison with diferente years? for example show in week 1 of 2022 the value of week 1 of 2021.
I think this will work :)
Total Sales LY =
VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1
VAR Result =
CALCULATE(
[Total Sales],
dimDate[Year_ISO] = LastYear
)
RETURN
Result
@@HowtoPowerBI This is just what I wanted to ask!!! Thanks, Bas!
Now to hunt for th number to use for first satuday of feb starts the year
simply bravo Bas !! :) 🤩👌
thank you for watching again Vignesver 😊🙂
@@HowtoPowerBI i am your regular fan 😀
U are amazing bro
Thank you so much for watching! 😊
How to deal with leap year please?
the logic of a leap year is captured in the date table as in the video
Merci
I appreciate 👍👍👍👍
thx Shrikant! 😊
Can you help me find job in power bi.
Not really but i keep my fingers crossed 🤞 all the best to you!
Was waiting for how to integrate weeks to Fiscal year..
that topic deserves a video on its own 🙂 however it comes down to adding columns for the fiscal year and week to your date table - similar logic as in this video
so you're telling me that a free looker studio has a more convenient date manipulation and date drill downs?
21 should be available as a suggestion lol
awesome content, but I wish that the ratio of power bi : your face in videos, would be more in favour of power bi.
thx for the feedback 🙂
Bingooo
You haven't notice that leap years starting on saturdays have 54 weeks... 1+52*7+1 = 366.
Won't a little correction every 28 years being needed, being the next one on 2028?
ISO only has 52 in 2028 and in 2020 53. don't know about the US version, their systems have no logic and should not be used in my opinion.
FIRST!
??ｏ?ｏ?? ?
Nah.... Too complex. I would rather use python with chat GPT for bug fixing.