How to compare data in different date ranges

[v-kongfanf-msft]

Scenario:

Sometimes we may want to compare data in different date ranges freely. The date range is not a standard range like year, month. We hope to let users use slicers to choose whatever ranges they want to compare. So how to achieve this goal?

In this article, I will show you how to achieve it with a simple method.

Expected Result:

Consider the scenario in the picture, we want to compare data of two different categories in two different date ranges in a column chart. We will apply two date slicers for users to select date ranges separately for comparison. In the column chart, we use one color to represent the first date range and another color for the second date range.

When selecting dates from January 1 through January 14 for slicer 1, and dates from January 15 through January 20 for slicer 2, the column chart will display the corresponding sales data in two date ranges for each category.

Sample Date:

The sample table includes Date, Type, Sales, which provides the sales of delivery and packaging in the January 2024.

How:
To achieve the expected result, we can follow the steps below.
1. Create two date slicers based on two date tables.

2. Create the new measure to calculate values based on slicer1 date range.

date ranges 1 =
VAR start_date =
    MIN ( 'Table slicer1'[Date] )
VAR end_date =
    MAX ( 'Table slicer1'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table test'[Sales] ),
        FILTER ( 'Table test', [Date] >= start_date && [Date] <= end_date )
    )

3. Create the new measure to calculate values based on slicer2 date range.

date ranges 2 =  

VAR start_date = MIN('Table slicer2'[Date]) 

VAR end_date = MAX('Table slicer2'[Date]) 

RETURN 

    CALCULATE(SUM('Table test'[Sales]), FILTER('Table test', [Date] >= start_date && [Date] <= end_date)) 

4. Create the clustered column chart, drag the Type field into the X-axis and drag the date ranges 1, date ranges 2 measures into the Y-axis.

5. Select the two slicers. Select dates from January 1 through January 14 for slicer 1, and dates from January 15 through January 20 for slicer 2.

6. The sales of packaging and delivery based on slicer1 and slicer2 date range are shown in the column chart.

Summary:
The above content introduces how to use slicer to get date range and how to calculate the sales based on different date range slicers. Hope this article helps everyone with similar questions.

Author:Wisdom W.
Reviewer:Ula and Kerry

How to compare different date range sales in column chart.pbix