Power BI Calendar Table: My Favorite Trick! (Offsets/Relative Date Range)
Вставка
- Опубліковано 21 сер 2024
- Why is the Calendar table so important in Power BI? And what is the best way to create a Calendar Table? I show you in this video series.
*Some videos can only be found inside the Learn Power BI Course.
www.learnpower...
1) The Ultimate Calendar Table • Power BI: The Ultimate...
2) How to use the Ultimate Calendar Table • Power BI: The Ultimate...
3) Building the Ultimate Calendar Table*
4) Ultimate Calendar Table and the Fiscal Year*
5) My Favorite Calendar Table Trick (Offsets): • Power BI Calendar Tabl...
6) Using Dynamic Date Range with the Ultimate Calendar Table*
********************************
FREE Power BI Step-by-Step Tutorial www.learnpowerb...
👉 Download Lesson PBIX Files at www.learnpowerb...
✅ Subscribe to always get my latest videos: goo.gl/P79Hcr
👪 Join our Facebook Group / talkpowerbi
===Most Popular Playlists===
• Power BI Beginner Tutorial: goo.gl/KRRkPG
• Ultimate Calendar: goo.gl/pyki4K
• Power BI Data Modeling and more: goo.gl/WNtJFv
⚡Power On!⚡
-Avi Singh
www.LearnPowerB...
www.AviSing.com
Just had to say this calendar you built is gold!!! Thank you.
Hi Avi. I cannot seem to find the Building the Ultimate Calendar video. Please help
Solid AF
Hi Avi, Thanks for great videos I still dont know how to compare Apple to Apple , I wonder which one used is it Curday or CurYear offset
Avi, this is a great idea. I am going to have to include that in my next report. I hope all is going well with you.
Great set of videos... im curious if there is a way to add holidays or if that has to be added manually with an external table
Oh yes. Just what I needed! Thanks so much.
This is so smart.
I did notice a logic error for his date offset stuff. He wants to be able to compare year-to-date. But, if he treats the current date as "past" then he could be treating last year's complete day to partial data from today. The current date should be "future", so
FutureDate =
if [Date] < CurrentDate
then "Past"
else "Future"
A small detail but it's important to recognize the small details when doing this stuff.
Hey! I can understand, how offsets cut off the current year, but have no idea how they cut off the corresponding months of the previous year. How come that the Sept16 cuts of with CurMonthOffset
Super great video, thanks alot
Hi Alex Havermans,
My pleasure!
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Hi Avi! Great tutorial! Can you update the Ultimate Calendar Table to include Offset by Weeks? Or provide some guidance on how to manually create fields to include Offset by Weeks so I can update it myself please? Thank you!
Keith, head to the downloads page and download the Ultimate Calendar Files again. I updated the table with Week Offsets (uses Sunday as the start of the week). Great question...I loved the exercise (although not sure I got the most elegant solution). I would be posting the video explaining the steps real soon.
⚡Power On!⚡
-Avi
Thank you so much for updating the table with Week Offsets Avi! This Ultimate Calendar Table has catapulted my reports to the next level. I can't thank you enough and I look forward to your next videos!
where can i download the ultimate calender table
How is the calendar table updated on daily basis? Meaning how will the offset columns will keep Changing daily?
Thanks soo soo oo oo much.. It helped me a lot.
Hi Adnan Kashem.
My pleasure! Glad this video resonated to you.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Avi, great stuff. One question. We don't close the month until the 5th business day after the end of the month, so for example on July 3 the last closed month is gong to be May. Thus using CurrentMonthOffset of -1 doesn't work for the first 5 business days of the month. Any ideas?
I have the same question, did you ever work it out?
In commercial world, we call "YTD-YearToDate" (Past) and "YTG-YearToGo" (Future)
Is it possible to use the Ultimate Calendar Table to factor weekends out of a date range? Does anyone have any suggestions on the best way to do this?
Hi Avi, I was wondering if it is possible to pull the same exact data from 2 different tables with one table having a -1day offset
Would it be a bad idea to just have the date range of the calendar set as MIN(sales date), MAX(sales date)? Assuming the only dates you were working with was sales transactions?
Ha! The debate to Truncate your Calendar (to match your Data) or Not is like Apple vs. Windows :-)
I'm firmly on the side of NOT truncating the Calendar. Truncating as you suggest makes some things easier (YTD formulas as they show in reports), but my biggest problem is that inevitably I end up with Multiple Datasets (as the model grows) and it becomes harder to decide the right date range for your model. I find it simpler to keep Full Years only.
You could start with Truncated (Mi n/Max approach) if you like and later change it to Full Years when you need.
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.UA-cam.com/PowerBIPro/Live **
Avi Singh - PowerBIPro thanks for the quick reply! Ha yeah that makes sense and I can already think of other scenarios where it would break (like forecasting). I just need to find some way to limit the range of the date slider so it doesn’t have an end date of December when it’s only March
I usually use the Ultimate Calendar (see video link below, you would also be able to download my Ultimate Calendar)
...and use the Future Dates or Month Offset Filters to exclude Future Dates/Months.
ua-cam.com/video/XjVLaVLluYE/v-deo.html
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.UA-cam.com/PowerBIPro/Live **
Avi, how would i go about grouping offset, for example having a radio button for last 6 months
+Taariq Toffar Great question. Just add a Custom Column in the Query Editor based on the CurMonthOffset. So if it's -1 to -6 then 'Past 6 Months'.
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.UA-cam.com/PowerBIPro/Live **
Hello Avi. Great Video. Why did you use the "Future" "Past" solution for the days instead of the Offset "is less than 0" solution as for years/months/quarters?
Michael Eisele, I used Future/Past, as that can be easily used in a Slicer (if I ever need to use it that way).
Ultimate Calendar download link is not working
After applying the ultimate calendar table, the dates are all jumbled up. Can anyone help me with this?
can u explain how you created calendar table ?
Hi Avi, excellent tutorials! I sum myself to the above request. Couldn't find the video explaining how you built the calendar.
Where is the video for how you build the ultimate calendar table?
Yee.. first like .. first comment :D