Power bi group by date range

by

In this power bi tutorial, we will see power bi group by date range. And also we will discuss the below points:

  • Power bi group by date range
  • Use the power bi switch function to group by date range

Power bi group by date range

Here we will see how to group a series of dates into date ranges in power bi.

Here I am using the below sample data to create a power bi group by date ranges using the Power BI desktop.

Power bi group by date range
Power bi group by date range
  • Now we will create which will show the start and end dates for each name (episode name) by using the Calculated column.
  • We will use the two calculate column, first calculated column we will calculate to show the series of boundries, which will point this date is start date and this is end date of series.
  • Second column will show the Last date of the Episode/series.
  • To create this column (series of boundaries) we will create a new column, Go to Modelling tab -> Click on the new column.
  • Dax expression:
Series Boundaries = 
VAR PreviousName =
    CALCULATE (
        MIN ( Table1[Name] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( [Index] ) - 1 )
    )
VAR Previous_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( [Index] ) - 1 )
    )
VAR Next_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( [Index] ) + 1 )
    )
VAR Next_Name =
    CALCULATE (
        MIN ( Table1[Name] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( [Index] ) + 1 )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( [Name] <> Next_Name, [Name] <> PreviousName ), "Isolated",
        AND ( [Name] <> Next_Name, Previous_Date <> [Date] - 1 ), "Isolated",
        OR ( PreviousName = BLANK (), PreviousName <> [Name] ), "Series Start",
        Next_Date = BLANK (), "Series Start",
        AND (
            AND ( Previous_Date <> [Date] - 1, [Name] = PreviousName ),
            Previous_Date <> [Date]
        ), "Series Start",AND(PreviousName=[Name],Next_Name=[Name])&&AND(Previous_Date=[Date]-1,Next_Date=[Date]+1),"Series Middle",
        "Series End"
    )

In the below screenshot, you can see the column of series boundaries

Power bi group by date range
Power bi group by date range
  • Now we will create the calculated column which will show the last date of the series.
  • For this click on the Modelling tab, then click on new column from the ribbon.
  • Then write the DAX formula
Last Date = 
VAR myindex = [Index]
VAR Next_Date =
    CALCULATE (
        FIRSTNONBLANK ( 'Table1'[Date], 1 ),
        FILTER (
            ALLEXCEPT ( 'Table1', 'Table1'[Name] ),
            'Table1'[Index] >= EARLIER ( 'Table1'[Index] )
                && 'Table1'[Series Boundaries] = "Series End"
        )
    )
VAR verynextdate =
    CALCULATE (
        MIN ( Table1[Name] ),
        FILTER ( Table1, Table1[Index] = myindex + 1 )
    )
RETURN
    SWITCH (
        TRUE (),
        [Series Boundaries] = "Isolated", [Date],
        'Table1'[Series Boundaries] = "Series Start"
            && ISBLANK ( Next_Date ), [Date],
    [Series Boundaries] = "Series Start", Next_Date
    )

In the below screenshot you can see the name of the series is grouped by date range i.e series start date and end date.

Power bi group by date range
Power bi group by date range

Read Power bi date format

How to use Power bi switch function to group by date range

Here we will see how to use the power bi switch function to group by date range.

  • I am using the below sample data to show the Cost between the purchase date.
  • For this we will create calculated column, which will show the result like last 15 days which product get sold and the amount of sales.
Use the power bi switch function to group by date range
Use the power bi switch function to group by date range
  • Load the data using get data in power bi desktop.
  • Now we will create a calculated column by using switch function, in which we will show the purchase cost in between the date range.
  • Click on the modelling tab in the ribbon then click on the new column, to create a calculated column.
  • Then write the below DAX function:
Aging bucket = SWITCH(TRUE(), AND(DATEDIFF(Sheet1[Date], TODAY(),DAY)>=0, DATEDIFF(Sheet1[Date].[Date],TODAY(),DAY)<=15), "0-15 days",
AND(DATEDIFF(Sheet1[Date], TODAY(),DAY)>=16, DATEDIFF(Sheet1[Date].[Date],TODAY(),DAY)<=30), "16-30 days",
AND(DATEDIFF(Sheet1[Date], TODAY(),DAY)>=31, DATEDIFF(Sheet1[Date].[Date],TODAY(),DAY)<=59), "31-59 days",
DATEDIFF(Sheet1[Date].[Date], TODAY(),DAY)>=60, "60 days")
Use the power bi switch function to group by date range
Use the power bi switch function to group by date range
  • Now we will create a visualization to show the purchase amount in between the date range
  • Select Slacked bar chart from the visualization pane.
  • In the Axis field, drag and drop the Aging bucket( calculated column) from the field pane
  • In the value field, drag and drop the product cost column from the field pane.
Use the power bi switch function to group by date range
Use the power bi switch function to group by date range

In the below screenshot you can see the product cost between the date range 0-15 days, 16-30 days, 31-59 days, and 60+ days.

Use the power bi switch function to group by date range
Use the power bi switch function to group by date range

You may like the following Power BI tutorials:

In this power bi tutorial, we learned how to implement power bi group by date range. And also we discuss the below points:

  • How to create power bi group by date range with example
  • How to use the power bi switch function to group by date range
>