Oh my...! You DIDN'T KNOW this about WEEKS?!

Рет қаралды 53,756

Күн бұрын

Understand how to analyze by week in Power BI
Analyzing by weeks can be more insightful than by months, however it can be quite trick. Let me give you the 2 main concepts you need to know when doing time intelligence on weeks.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:28 Why break down by weeks
01:14 Adding weeks to a date table
03:14 Adding ISO weeks (EU standard)
06:05 Adding ISO years
07:31 Week calculations
15:09 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZfaq: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Пікірлер: 105

@mdtauhidalam9349 8 ай бұрын

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

@evelynlivant6860 Жыл бұрын

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!

@alicedwonderland7733 2 жыл бұрын

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.

@dcpowered 2 жыл бұрын

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?

@JustRandomExplorer 2 жыл бұрын

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!

@JustRandomExplorer 2 жыл бұрын

And thanks for giving the formula for ISO_Year! Great content!

@DrumingSession 2 жыл бұрын

Amazing - whenever I have a question pop into my head, you have a video to answer it. Great content as always!

@HowtoPowerBI 2 жыл бұрын

Happy to have the perfect timing! Hopefully next time the same :)

@utkudulger517 2 жыл бұрын

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👍👋🏻

@HowtoPowerBI 2 жыл бұрын

haha in just in time 😅happy I could help! 😊

@punith26 2 жыл бұрын

Watching all your videos and trying in my system. Thank you😄 !!

@HowtoPowerBI 2 жыл бұрын

awesome Punith, thanks! 😊😎👊

@tukaramjogdand7023 2 жыл бұрын

Thank you Bas, Its really amazing. Thank you for sharing!

@unnikrishnansanthosh 2 жыл бұрын

i was searching for this and here you are, thank you BAS

@HowtoPowerBI 2 жыл бұрын

awesome! happy it helped Santhosh

@genevieve3676 Жыл бұрын

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

@innocentigiwa5509 2 жыл бұрын

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.

@ItsMeProday83 6 ай бұрын

Nicely done, thanks for this!

@lucasvillamor4409 9 ай бұрын

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?

@TheTiM731 Жыл бұрын

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?

@MatusiSK Жыл бұрын

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.

@EYEmaginary 2 жыл бұрын

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.

@mehdihammadi6145 2 жыл бұрын

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

@MrRobvanzutphen1 2 жыл бұрын

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 2 жыл бұрын

can you share please same week last year measure or approach ?

@HowtoPowerBI 2 жыл бұрын

@@marounsader318 Total Sales LY = VAR LastYear = YEAR( MAX(dimDate[Date]) ) -1 VAR Result = CALCULATE( [Total Sales], dimDate[Year_ISO] = LastYear ) RETURN Result

@marounsader318 2 жыл бұрын

@@HowtoPowerBI thank you so much 🙏

@cindypayet281 5 ай бұрын

Very, very USEFUL. A great big thank you !

@user-jf4vt6nb4m 2 жыл бұрын

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?

@brianleddo7342 2 жыл бұрын

Great vid! Great channel!

@bayarri81 Жыл бұрын

Great video! Could you use TOTALYTD with this date table? Thanks in advance!

@waqarhanif4402 2 жыл бұрын

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.

@kammarisairam9445 Жыл бұрын

Great stuff by you.... 😊.

@s3icc0 Жыл бұрын

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?

@Yourivanhien 2 жыл бұрын

Heel nice Bas, dank!

@alirohman57 5 ай бұрын

Great video. Thanks! Can you tell me, what should I change for the calculation if my week is Sunday to Saturday?

@HowtoPowerBI 5 ай бұрын

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

@laiqmehdi8704 2 жыл бұрын

Superb superb superb god bless u always

@HowtoPowerBI 2 жыл бұрын

thank you so much Laiq! 😊

@rajnishrajput2367 2 жыл бұрын

Nice trick as always!! Thanks I would like to know the process to create such elegant KZfaq 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

@HowtoPowerBI 2 жыл бұрын

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 🙂

@rajnishrajput2367 2 жыл бұрын

@@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 KZfaq videos.

@workstuff5253 2 жыл бұрын

I hate working with weeks. This will be a nice reference video - thanks

@jpfm79 2 жыл бұрын

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.

@HowtoPowerBI 2 жыл бұрын

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

@alicedwonderland7733 2 жыл бұрын

@@HowtoPowerBI This is just what I wanted to ask!!! Thanks, Bas!

@jordanclark4243 8 ай бұрын

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 :)

@septiawanpambudi3571 7 ай бұрын

try ((INT([Date]-Weekday([date], 3) - _StartWeek1)+7)/7)

@letbloodline 2 жыл бұрын

In European format using weekid_iso or -7 days doesn’t give the exact same results? Am I missing something?

@StefanDorien Жыл бұрын

i hoped you would have a solution for this one in M (power query)

@rrayss 6 ай бұрын

Merci

@alexmcqueen5563 2 жыл бұрын

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?

@HowtoPowerBI 2 жыл бұрын

oh indeed 😅😄 happened 5 times since 1900 (1916, 1944, 1972, 2000, 2028, 2056)

@HowtoPowerBI 2 жыл бұрын

here is my adjusted version "WeekID", WEEKNUM([Date], 1) + (YEAR([Date]) - _MinYear) * (53 + INT(MOD(YEAR([Date])-1915,28)=1)),

@govardhanbola1195 2 жыл бұрын

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

@HowtoPowerBI 2 жыл бұрын

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

@vignesversb 2 жыл бұрын

simply bravo Bas !! :) 🤩👌

@HowtoPowerBI 2 жыл бұрын

thank you for watching again Vignesver 😊🙂

@vignesversb 2 жыл бұрын

@@HowtoPowerBI i am your regular fan 😀

@br3185 2 жыл бұрын

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?

@HowtoPowerBI 2 жыл бұрын

yes, you can use the sort by column feature -> see explanation here kzfaq.info/get/bejne/ipygaNuqmJu5gHk.html

@catherinegould6662 2 жыл бұрын

Would also be very interested in 2 week scenarios, would that be just adjusting the increasing week count with 2 weeks instead of 1?

@HowtoPowerBI 2 жыл бұрын

yes indeed 🙂 just replace "-1" with "-2" and that's it

@roupyabehera9006 2 жыл бұрын

@@HowtoPowerBI When the year changes, and we are doing -1 for previous week; are we including all the 7 days in the previous week?

@SupernovaSmith 5 ай бұрын

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?

@Twyfie1 Ай бұрын

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?

@HowtoPowerBI Ай бұрын

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

@dpecoraro66 2 жыл бұрын

Bas, how do you sort the table by multiple columns?

@HowtoPowerBI 2 жыл бұрын

Hold shift and click on the column headers

@angeliquedefays2991 2 жыл бұрын

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 :'(

@HowtoPowerBI 2 жыл бұрын

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 -> kzfaq.info/get/bejne/g85zZ92bl8jdcoU.html

@angeliquedefays2991 2 жыл бұрын

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

@user-ne8qc3fi2p 11 ай бұрын

One Question, DAX to get week number starting from Saturday to Friday.

@shrikantbadge3978 2 жыл бұрын

I appreciate 👍👍👍👍

@HowtoPowerBI 2 жыл бұрын

thx Shrikant! 😊

@japumon 2 жыл бұрын

U are amazing bro

@HowtoPowerBI 2 жыл бұрын

Thank you so much for watching! 😊

@joecharest9724 5 ай бұрын

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.

@joecharest9724 5 ай бұрын

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

@HowtoPowerBI 5 ай бұрын

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.

@mohsinalam8085 Жыл бұрын

When I write 21 as the second argument to weeknum , it gives error as unrecognised parameter

@Jay-bp1yx 6 ай бұрын

0:00 - 4:50

@kostaszogo21 2 жыл бұрын

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

@HowtoPowerBI 2 жыл бұрын

Awesome :) this is magic, no?

@kostaszogo21 2 жыл бұрын

@@HowtoPowerBI This is totally "Houdini and Copperfield together" stuff...

@agapheputrasusilo741 2 ай бұрын

great video but i need to replay it multiple times because im not a fluent english speaker. thanks btw

@HowtoPowerBI 2 ай бұрын

thanks, hope the auto translation subtitles help a bit

@MrSparkefrostie Жыл бұрын

Now to hunt for th number to use for first satuday of feb starts the year

@patrickkinbonso1809 5 ай бұрын

How to deal with leap year please?

@HowtoPowerBI 5 ай бұрын

the logic of a leap year is captured in the date table as in the video

@ahmetyildirim7268 2 жыл бұрын

Bingooo

@syedmohdabbas488 Жыл бұрын

Can you help me find job in power bi.

@HowtoPowerBI Жыл бұрын

Not really but i keep my fingers crossed 🤞 all the best to you!

@derrickkuria4206 2 жыл бұрын

Was waiting for how to integrate weeks to Fiscal year..

@HowtoPowerBI 2 жыл бұрын

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

@anhnguyenduy6861 2 жыл бұрын

21 should be available as a suggestion lol

@himaya-relaxsleep1410 15 күн бұрын

so you're telling me that a free looker studio has a more convenient date manipulation and date drill downs?

@lazybones77 2 жыл бұрын

awesome content, but I wish that the ratio of power bi : your face in videos, would be more in favour of power bi.

@HowtoPowerBI 2 жыл бұрын

thx for the feedback 🙂

@raeesg 2 жыл бұрын

FIRST!

@johnpatrickwoolbright4232 2 жыл бұрын

??ｏ?ｏ?? ?

@jaimesegurapena 2 жыл бұрын

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?

@BjornV1976 2 жыл бұрын

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.