cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

test1.pngHi,

 

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!

.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

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

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User IV
Super User IV

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

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Highlighted
Super User I
Super User I

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

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User IV
Super User IV

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

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Community Support
Community Support

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

Hi @pprasad2 ,

 

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

v-lionel-msft_0-1600925504389.png

 

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

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

v-lionel-msft_0-1600915545101.png

 

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.

 

Highlighted
Frequent Visitor

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

Thanks Owen.

Both of the solutions worked!

Highlighted
Frequent Visitor

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

Thanks Amit.

 

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

 

Highlighted
Frequent Visitor

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

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??

 

 

pprasad2_0-1601232477351.png

 

Regards

DP

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (807)