Comparing Data Across Date Ranges

[amitchandak]

Data and its analysis are the needs of the hour. Business intelligence and analytics tools are enablers for a data-driven culture. Often, comparing data between two time slices is commonly sought after. Say, for example, one would like to compare sales of this year vs. last year or look at a custom period like sales of this year’s Christmas season vs. last year’s Christmas season. You may want to measure the sales performance of those two periods and find the growth or decrease.

Power BI offers you various ways to do this. Here, you can find one of the ways of doing the time slice comparison.

I had solved the use case by using 2 independent date slicers and used the power of DAX to make it work. Sharing our exploration here for the benefit of the community.

For the purpose of demonstration, we create a per-day sample sales data table in Excel. We also create date calendars in independent sheets representing each time slice. Alternatively, you can also create a calendar using the Calendar function in Power BI itself.

Now, import the created data in Power BI using the Excel import wizard.

We have done some renaming and created one more version of the calendar. Please note that we have not performed any join here with the Sales table. All three tables are independent tables. The tables are as follows:

• Sales and Date: These will be used for the Current Date range
• Compare Date: This will be used for the Prior Date Range.

Now let’s visualize. The sales table has two items: Sales Amount and Sales Date, we have created a bar chart to view Sales by Year by Year. And we have added two date slicers one from each table (Current/Date — Date filter and Compare — Compare Date).

Sales measures do not change or respond to the changes in any of the date slicers. Now to see DAX in action, let us create two formulas to calculate Current Sales and Prior Sales respectively. Below is the code for Current Sales.

Current Sales = (
VAR _Cuur_start = Min(‘Date’[Date Filer])
VAR _Curr_END = Max(‘Date’[Date Filer])
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END ))

In the same manner, we added Prior Sales using the second date slicer.

Prior Sales = (
VAR _Cuur_start = Min(‘Compare Date’[Compare Date])
VAR _Curr_END = Max(‘Compare Date’[Compare Date])
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END )
)

Each of these metrics are bound to their own slicer. The next step is to add them to the graph. Now, we will experiment to observe the effect of changes in dates. This shows us how the power of DAX can be utilized to create complex calculations easily to override the in-built power bi multi-component interaction capabilities.

We can observe the impact of slicers on their respective measures.

We also added one more graph to exhibit how it can be used to compare against multiple dimensional data. For example, using this we can compare item-wise, brand-wise sales across various periods.

On top of this, we can create change % (Period over Period). We can use similar logic to calculate This Year, This Qtr., This Month, Last Year, Last Qtr., Last Month, etc. All of the above can be managed with one date slicer. You just need to choose a suitable date range better suited for your purpose.

Here is another example using a different date slicer to calculate some of the measures mentioned above. This is on different data set than the one we have used above.

Hope you find it useful. Please, feel free to reach out to us with your questions or comments.