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

Подписаться 220 тыс.

Просмотров 54 тыс.

50% 1

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 RU-vid: 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

Наука

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 105

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

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

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

@tukaramjogdand7023 2 года назад

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

@punith26 2 года назад

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

@HowtoPowerBI 2 года назад

awesome Punith, thanks! 😊😎👊

@ItsMeProday83 6 месяцев назад

Nicely done, thanks for this!

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

@unnikrishnansanthosh 2 года назад

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

@HowtoPowerBI 2 года назад

awesome! happy it helped Santhosh

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

@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

@cindypayet281 6 месяцев назад

Very, very USEFUL. A great big thank you !

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

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

@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

@brianleddo7342 2 года назад

Great vid! Great channel!

@kammarisairam9445 Год назад

Great stuff by you.... 😊.

@Yourivanhien 2 года назад

Heel nice Bas, dank!

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

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

@TheTiM731 2 года назад

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?

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

@workstuff5253 2 года назад

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

@laiqmehdi8704 2 года назад

Superb superb superb god bless u always

@HowtoPowerBI 2 года назад

thank you so much Laiq! 😊

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

@bayarri81 2 года назад

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

@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

@rrayss 6 месяцев назад

Merci

@vignesversb 2 года назад

simply bravo Bas !! :) 🤩👌

@HowtoPowerBI 2 года назад

thank you for watching again Vignesver 😊🙂

@vignesversb 2 года назад

@@HowtoPowerBI i am your regular fan 😀

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

@rajnishrajput2367 2 года назад

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

@shrikantbadge3978 2 года назад

I appreciate 👍👍👍👍

@HowtoPowerBI 2 года назад

thx Shrikant! 😊

@japumon 2 года назад

U are amazing bro

@HowtoPowerBI 2 года назад

Thank you so much for watching! 😊

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

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

@StefanDorien Год назад

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

@agapheputrasusilo741 3 месяца назад

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

@HowtoPowerBI 3 месяца назад

thanks, hope the auto translation subtitles help a bit

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-T6p5xy35UPE.html

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

@dpecoraro66 2 года назад

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

@HowtoPowerBI 2 года назад

Hold shift and click on the column headers

@user-ne8qc3fi2p 11 месяцев назад

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

@Jay-bp1yx 6 месяцев назад

0:00 - 4:50

@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

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

@SupernovaSmith 6 месяцев назад

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?

@letbloodline 2 года назад

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

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

@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 -> ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MhC4zj2byBQ.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

@MrSparkefrostie Год назад

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

@mohsinalam8085 Год назад

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

@Twyfie1 2 месяца назад

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 2 месяца назад

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

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

@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

@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

@ahmetyildirim7268 2 года назад

Bingooo

@anhnguyenduy6861 2 года назад

21 should be available as a suggestion lol

@syedmohdabbas488 Год назад

Can you help me find job in power bi.

@HowtoPowerBI Год назад

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

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

@himaya-relaxsleep1410 25 дней назад

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

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