cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User I
Super User I

@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

Super User IV
Super User IV

@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
Super User I
Super User I

@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

Super User IV
Super User IV

@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

Community Support
Community Support

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.

 

Thanks Owen.

Both of the solutions worked!

Thanks Amit.

 

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

 

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors