ASK MAVEN: Fiscal Calendars with Power BI + DAX
Вставка
- Опубліковано 15 лип 2024
- Learn from Maven Analytics instructors as we explore some of the most interesting analytics & business intelligence questions our students have asked!
In this video, Lead Power BI Instructor Aaron Parry dives into fiscal calendars in Power BI, and how to write DAX measures for custom time intelligence calculations.
★★★★★ Looking for our best-selling Excel, Power BI, Tableau, and SQL courses?
Sign up for a 100% FREE account at Maven Analytics (no cc required), and start learning today:
👉 www.mavenanalytics.io/
Follow Maven Analytics on LinkedIn for monthly challenges, free data sets, blog posts, and more:
👉 / 35519409
Video Timestamps:
0:15 - Introducing the question
0:50 - The Maven Roasters data model
1:14 - Intro to 4-4-5 and 4-5-4 calendars
1:55 - Fiscal calendar table in Power BI
4:00 - DAX measure for MTD sales
5:03 - DAX measure for Previous Month sales
#powerbi #dax
This is a great explanation as always. One additional custom calculated column that I've added to our 4-4-5 calendar is called period offset. The beauty of this feature is that the current period is always 0 and is recalculated at every refresh. The offset of previous period is 1, period before that is 2, and so on. Future periods are -1, -2, etc. In this way you can create measures for previous period by simply identifying the period being filtered and add 1. If you need the previous 3, 6, 12 periods simply create a measure to set the selected visual filter period to VAR _selectedPeriod = SELECTEDVALUE() then filter >=_selectedPeriod + 1 &&
Outstanding Aaron, thank you!
Thanks Fred!
Just what I was looking for, thanks!!
Happy to hear it!
Absolutely brilliant
Thanks Saraswathi!
New Content. Love it. 😊
Thank you Bolaji!
Have been looking everywhere for this fix! Thank you. Do you have one for QTD or YTD?
This is brilliant. Do you have a video with the same logic but applied to Fiscal QTD ?
This is amazing! Is there available to download the PBI file?
Hi, I would like to know how much time do I have to take the exam PL-300 after making the purchase? Are we talking about 2 weeks? Thank you :)
great video, made me to subscribe your channel ;)
How can we calculate % utilization based on hours/efforts spend by a resource. Like when I want to calculate his/her weekly utilization, it should be /40. In case of monthly utilization, it should /40*4 likewise for quarter /40*4*3.
Any sort of DAX we can create to keep the denominator dynamic?
Keep in mind that custom fiscal calendars are still built at the day level. So, creating a measure to calculate utilization (hours worked divided total hours planned/scoped) would still work. What you need to keep in mind is the initial filter context for the measure (week, month, quarter) as this will change how your utilization measure is aggregated.