Creating a MTD/QTD/YTD Time Intelligence Slicer in Power BI

Part one of four in a series. Learn how to leverage relationships and DAX calculated tables to create a time intelligence slicer in Power BI. The end result will perform faster and create a better user experience versus creating lots of unique time intelligence DAX measures.

RELATED CONTENT 🔗

BECOME A CHANNEL MEMBER 🎉

LET'S CONNECT! 🧑🏽‍🤝‍🧑🏽 🌟

VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:12 - Content Intro
1:08 - Demoing the Solution
2:43 - Showing the Model Design
3:29 - Showing the DAX Measures and Tables
5:45 - Discussing the DAX Code
8:17 - Summary Discussion

HAVENS CONSULTING PAGES 📄

EMAIL US AT 📧

#PowerBI #PBI #microsoftpowerbi #HavensConsulting #powerplatform #microsoft #businessintelligence #datascience #office #data #digitaltransformation #dataanalytics #tableau #excel #powerapps #datavisualization #dashboard #sharepoint #python #bi #analytics #cloud #azure #bigdata #sqlserver #software #sql #dynamics #dataanalysis #yammer #microsoftpowerbi #onedrive #machinelearning #bhfyp #powerbidesktop

[matheusbergamo]

For those, like me, that tried to make it work with WTD as well, i made it like this:
var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate, 2), YEAR(Data[Data])=YEAR(TodayDate))

[atifmir]

This is a lovely time intelligence addition. I'll be adding this to one of my reports. Thanks

[adamsidebottom]

Hey there, a really well explained video. This was the first real DAX work I've done and I managed to follow it and create the functionality. I even used the prinicples to add a "Yesterday" in there as well!

[satishkumardommeti]

Excellent. Simple yet powerful approach.

[katiawei]

Brilliant solution to YTD MTD buttons. Thank you much for the sharing.

[axelveronr]

Hi! thank you for this wonderful video! I have a little question, what is the information from Data[month year]? because I have a table with 12 dates (start year date 09/30/2019 and end year date 08/31/2020) but when I replace the information with my table it got an error : "The start date in Calendar function can not be later than the end date". Could you please let us know how can we replace the table month year?

[dnbzivp]

I would like to add previous Month to the selection + 3prior months
PreviousMonth = if current month is march, previous month would be 01/02/2023 - 28/02/2023
3 prior months : if current month is march, prior 3 months would be 01/12/2022 - 28/02/2023

[AndyBearYT]

if you wanted to change the order of the items in the slicer so it shows YTD, QTD, MTD you can add a column to the table in the code and then sort the selections based on that. For Example - ADDCOLUMNS(
CALENDAR(YearStart, TodayDate),
"Selection", "YTD", "Order", 1)

[junaidmalik]

Man i am like waiting restless for every video or ur's.

[raphaelokoye]

Nice job!. How do I modify the formula to accommodate YTD LY, MTD LY and QTD LY?

[zachdigan]

This is exactly what I was looking for! Two questions: 1) is it possible to apply this selector so that you can do YoY calculations or would you need 2 selectors? 2) How did you get around the multiple column error?

[Doffskii]

Nice use of relationship! I will definitely try this out. Normally I use the pattern with disconnected slicers and switch formulas so I'm excited to see this from a performance perspective. If the selection table can be calculated in PQ it can be stored in data flows for reuse in other models, but alot more fun crunching out a good DAX code ;)

[kurzashane]

If our date table has relationships both active and inactive to other tables how do we avoid a circular dependency?

[jonathandupuis]

Thank you so much for this tutorial! Was very educative and well explained! Impressive work!

[mayurpawar]

Thank you for this video! Quite Impressive. I'm working on something, based on the video MTD/QTD/YTD I created the selection and the calculations seem to work fine. I had one question can we also compare with PMTD/PQTD/PYTD as well at the same time basis the MTD/YTD/QTD selections. Thanks

[mwaltercpa]

Great practical solution Reid! Thank you! The Dax table using Union will be a nice add to my go-to tools, and faster calcs as a bonus!

[DDDMFZ]

Super video, very helpful.
I have a question: what if I would like to select a month so to calculate YTD and MTD of that month, without using any formula, could I make it? Thank you.

[kurzashane]

Is it possible to add a week to date selection to the panel?

[s_HipHop]

Great video. Playing with this (as a newbie to PBI) I have recreated it in PQ but found the broke a LY comparison DAX measure. There was an error with SAMEPERIODLASTYEAR. Do you know if this method is incompatible? As I am new to the platform, I am still trying to figure out what methods to use for the best, especially when it comes to date selections, interactions and calculations. Thanks

[jackeroo]

Wow kept it up and you will have a huge following soon!