Cool trick to create a week ID that continually rises, also from year to year!
@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!
@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! 😊
@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?
@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 🙏
@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.
@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
10 ай бұрын
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. 😊
@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 :)
@unnikrishnansanthosh
2 жыл бұрын
i was searching for this and here you are, thank you BAS
@HowtoPowerBI
2 жыл бұрын
awesome! happy it helped Santhosh
@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.
@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.
@cindypayet281
8 ай бұрын
Very, very USEFUL. A great big thank you !
@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?
@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!
@ItsMeProday83
8 ай бұрын
Nicely done, thanks for this!
@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
@rrayss
8 ай бұрын
Merci
@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.
@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
@laiqmehdi8704
2 жыл бұрын
Superb superb superb god bless u always
@HowtoPowerBI
2 жыл бұрын
thank you so much Laiq! 😊
@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.
@workstuff5253
2 жыл бұрын
I hate working with weeks. This will be a nice reference video - thanks
@SupernovaSmith
8 ай бұрын
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?
@brightonalbert3237
2 күн бұрын
can we go back wards i.e. from week no to week start date or week end date
@agapheputrasusilo741
5 ай бұрын
great video but i need to replay it multiple times because im not a fluent english speaker. thanks btw
@HowtoPowerBI
5 ай бұрын
thanks, hope the auto translation subtitles help a bit
@lucasvillamor4409
11 ай бұрын
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?
@brianleddo7342
2 жыл бұрын
Great vid! Great channel!
@kammarisairam9445
2 жыл бұрын
Great stuff by you.... 😊.
@Twyfie1
4 ай бұрын
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
4 ай бұрын
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
@Jay-bp1yx
8 ай бұрын
0:00 - 4:50
@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?
@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?
@alirohman57
7 ай бұрын
Great video. Thanks! Can you tell me, what should I change for the calculation if my week is Sunday to Saturday?
@HowtoPowerBI
7 ай бұрын
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
@letbloodline
2 жыл бұрын
In European format using weekid_iso or -7 days doesn’t give the exact same results? Am I missing something?
@Yourivanhien
2 жыл бұрын
Heel nice Bas, dank!
@user-ne8qc3fi2p
Жыл бұрын
One Question, DAX to get week number starting from Saturday to Friday.
@joecharest9724
7 ай бұрын
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
7 ай бұрын
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
7 ай бұрын
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.
@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
@bayarri81
2 жыл бұрын
Great video! Could you use TOTALYTD with this date table? Thanks in advance!
@mohsinalam8085
Жыл бұрын
When I write 21 as the second argument to weeknum , it gives error as unrecognised parameter
@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 KZitem 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 KZitem videos.
@jordanclark4243
10 ай бұрын
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 :)
i hoped you would have a solution for this one in M (power query)
@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 kzitem.info/news/bejne/tWymm6yvbGeGg3o
@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...
@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?
@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)),
@MrSparkefrostie
2 жыл бұрын
Now to hunt for th number to use for first satuday of feb starts the year
@dpecoraro66
2 жыл бұрын
Bas, how do you sort the table by multiple columns?
@HowtoPowerBI
2 жыл бұрын
Hold shift and click on the column headers
@ahmetyildirim7268
2 жыл бұрын
Bingooo
@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 -> kzitem.info/news/bejne/rp55mq6ga5SqdYY
@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
@patrickkinbonso1809
7 ай бұрын
How to deal with leap year please?
@HowtoPowerBI
7 ай бұрын
the logic of a leap year is captured in the date table as in the video
@himaya-relaxsleep1410
2 ай бұрын
so you're telling me that a free looker studio has a more convenient date manipulation and date drill downs?
@anhnguyenduy6861
2 жыл бұрын
21 should be available as a suggestion lol
@syedmohdabbas488
2 жыл бұрын
Can you help me find job in power bi.
@HowtoPowerBI
2 жыл бұрын
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
@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 жыл бұрын
??o?o?? ?
@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.
@primary4075
5 ай бұрын
Nah.... Too complex. I would rather use python with chat GPT for bug fixing.
Пікірлер: 107