Use the Power BI Switch Function to Group By Date Ranges

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.

5 Comments

  1. 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?

    Like

    Reply

    1. 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.

      Like

      Reply

      1. 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+”))

        Like

      2. 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+).

        Like

  2. 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!

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s