Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

amitchandak

Comparing Data Across Date Ranges

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.

 

Screenshot 2019-08-08 23.50.39.png

 

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

 

Screenshot 2019-08-08 23.15.14.png

 

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.

 

Screenshot 2019-08-08 23.20.55.png

 

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

 

Screenshot 2019-08-08 23.23.16.png

 

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.

 

Screenshot 2019-08-08 23.27.06.png

 

 

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.

 

Screenshot 2019-08-08 23.29.49.png

 

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.

 

Screenshot 2019-08-09 00.20.47.png

 

 

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.

 

comparePowerbi.png

 

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

 

Comments

@amitchandak  This is fantastic! It gets me 90% of the way to where I want to be. 

 

The one issue I am having is that if my 2 date slicers have no overlap my current measure goes blank... Any suggestions? 

 

@Kurtle, if the issue is not resolved. Please share pbix on the desktop  discussion.

@amitchandak It was because I had relationships between my current, prior and sales tables. Once I got rid of those everything worked. 

 

Anonymous

@amitchandak  How to create Date Filter table

In my case, I called Date as a date filter.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

hola buenas tardes 

 

muy buen al informacion!!, una consulta adicional, existe una manera que yo pueda multiplicar valores de terminos diferentess entre meses especificos, es decir tengo que realizar este calculo.

=costo kilo de mayo * participacion de junio

 

el costo kilo y la participacion ya estan calculados, solo me falta la formula para poder multiplicar entre el mes de mayo por junio, muchas gracias!!

@amitchandak ,

Thank you for this.

 

However how can I handle situations where I do not want to compare date to date. ie. 11/26/2019 to 11/26/2020?

 

for example for Thanksgiving it falls on different days of the year.  So I need to be able to compare the first day which maybe 11/28/2019 to 11/26/2020.

 

Currently my graphs look something like this:

Screenshot_022521_123631_PM.jpg

Is there a way we can force the 2nd date filter to only display the dates before the selected minimum date in the first date slicer and limit it to the same number of days?