In this latest video, I’ll explain how to use a handy DAX function in Power BI in order to group dates together for reporting. We’ll examine a dashboard that contains fields corresponding to purchase item, purchase date and purchase cost. We’ll then create a calculated column and use the SWITCH function in Power BI to perform our date grouping on the purchase date.
Watch the video to learn how to group dates into the following aging buckets, which can be customized to fit your specific need.
- 0-15 Days
- 16-30 Days
- 31-59 Days
- 60+ Days
If you are familiar with SQL, then you’ll recognize that the SWITCH function is very similar to the CASE statement; which is SQL’s way of handling IF/THEN logic.
Even though we’re creating a calculated column within Power BI itself, best practice is to push calculated fields to the source when possible. The closer calculated fields are to the underlying source data, the better the performance of the dashboard.
Smoak Signals | Tableau, Excel, SQL 
Following “Use the Power BI Switch Function to Group By Date Ranges”, I’m getting expressions that yield variant data-type cannot be used. Do you happen to know the fix?
LikeLike
It’s hard for me to say since I am not familiar with your data but generally that error occurs when trying to use multiple data types in the CALCULATE function. Make sure you’re only using the date data type in the function.
LikeLike
Thanks for the quick response. I believe I’m doing so, see below. It’s right from your easy to use video 🙂
30_60_90 Due Dates = SWITCH(TRUE(),
AND(DATEDIFF(Decision_List[Due Date].[Date],TODAY(),DAY)>=0,
DATEDIFF(Decision_List[Due Date].[Date], TODAY(),DAY)=16,
DATEDIFF(Decision_List[Due Date].[Date], TODAY(),DAY)=31,
DATEDIFF(Decision_List[Due Date].[Date], TODAY(),DAY)=60, “60+”))
LikeLike
You are only using 1 AND function and I dont see the verbiage that is returned for each time period i.e., “0 to 16 Days”, etc. Go back and double check the syntax from the video making sure you use multiple nested AND functions and have return verbiage for each time period (not just for 60+).
LikeLike
I’m not sure what happen to the other AND functions when I copied them, but everything is working beautifully now. Thanks so much for you assistance!
LikeLike