Current and previous date range slicers from a single date field and produce variance

[pprasad2]

Hi,

My user is very particular in having two date slicers, one for current date range and another for previous date range. I do have one date column in my table and also use calendar table as well. They always compare the metrics between current date range vs previous date range. I need to show the metrics as below:

1. Sales for the Current Date Range

2. Sales for the Previous Date Range

3. Variance between above two

I have attached a sample workbook for your refernce. My requirement is much bigger than that, but I do hope you might have some options to display two date range filters from the same column (or by using the date from data table and also from calendar date) and display the varinace from these two date ranges.

I appreciate your ideas/tips. Thank you!

.

[OwenAuger · Accepted solution]

@pprasad2 

I would recommend the method shown here:

DAX Patterns - Comparing different time periods 

You create a 'Comparison Date' table with an inactive relationship with the 'Date' table, and then write measures similar to the [Comparison Sales Amount] measure shown on the above page.

Regards,

Owen

[v-lionel-msft]

Hi @pprasad2 ,

Do your two slicers use the same date field? If this is the case, the difference can not be calculated.

So the date fields of the two date slicers must come from two date tables.

Please refer to @amitchandak  and @OwenAuger  's links.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@pprasad2 , refer to my blog on a similar topic. I have used one connected table and one disconnected table

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[pprasad2]

Thanks Amit.

Both of the solutions worked!. It was a huge relief. 

[OwenAuger · Accepted solution]

@pprasad2 

I would recommend the method shown here:

DAX Patterns - Comparing different time periods 

You create a 'Comparison Date' table with an inactive relationship with the 'Date' table, and then write measures similar to the [Comparison Sales Amount] measure shown on the above page.

Regards,

Owen

[pprasad2]

Thanks Owen.

Both of the solutions worked!

[pprasad2]

Hi @OwenAuger / @amitchandak 

It worked well for KPI boxes but did not work for Trendcharts. I created another date table and made an inactive relationship to Date Table & Comparison Date table, but seems like its not working.

How can I make this work for both KPI's cards as well as trend charts??

Regards

DP