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

Two dates from the same date column

Hi all, I have requirement to compare the sales(for example) for two different periods seperately in two different charts. 

Consider the below table as a sample where there is only one date field

 

MonthSales
Jan1000
Feb1500
Mar4100
Apr466
May4100
Jun5615
Jul5145
Aug7800
Sep4830
Oct5478
Nov4871
Dec6874

 

 

My client has a requirement to compare the sales from Jan to June with July to December in two seperate graphs even though we can use a single bar graph for the entire year data. So he wants to have two date pickers first one is "Select date" and the second date picker is "Compare to date"

 

So in the "Select date" I will have to select Jan to June and in the "Compare to date" I will have to select July to December or any date of choice. The problem here is both the date picker will be fetching from the same date column.

As PowerBI charts are interactive if I select Jan to June in the "Select date" it is going to mask July to December in the "Compare to date" and ultimately will display charts only for Jan to June. Kindly help me how this situation can be overcomed

 

6 REPLIES 6
BhaveshPatel
Community Champion
Community Champion

You can stop the interactions between the slicers & charts by changing the settings in the edit interactions option.

See the attached screenshot.

 

 

edit interactions.PNG

 

and in the other slicer, You can deselect the no of months not required in the page level filters, only keeping the months for which you required to show the value. 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Hi @BhaveshPatel the page level filter is going to be constant throughout. But Jan to June and July to December is just a sample I gave. It need not always be the same. Sometimes they might want to compare between Jan to March with July to Sept or Jan to March with Oct to December or between any two period for that matter like July to  Sept 2015 with July to  Sept 2016 . So I don't think Page level filter will be a good idea. Your thoughts ?

Hi @thanish,

From my perspective of view, as you want to compare sales between any periods, it is better to have another two calendar tables and create relationships among the three tables( calendar tables and sales table) as OwenAuger's post. That way, it is more convenient to select different months in the two slicers and compare sales.

 1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft  yes I was convinced with @OwenAuger approach and it seems to be good. But the apporach was produced on a monthly basis if it could be produced on a day format it would be great. We want the select and comparison between dates for other calculations also. Suppose we want to check the sales made by a employee from Jan to March and compare his sales for June to August(need not always be same and also need not always be in months, the comparison can also be in days like 15 or 20 days) we want to have something like what is in the attached photo in addition to what I have asked before like seperate charts.

 

So what you see X in the chart is the sales for the selected date and Y is the sales for the comparison date. In the table below you can see the Date/Date column which explains about the sales difference for the given selected and comparison period for an employee(this would not be required to be displayed though) which would be used to calculate the percentage in the next column.

If anybody could help me replicate the exact chart and table below it would be great

 

Untitled.png

 

Hi @v-yuezhe-msft  yes I was convinced with @OwenAuger approach and it seems to be good. But the apporach was produced on a monthly basis if it could be produced on a day format it would be great. We want the select and comparison between dates for other calculations also. Suppose we want to check the sales made by a employee from Jan to March and compare his sales for June to August(need not always be same and also need not always be in months, the comparison can also be in days like 15 or 20 days) we want to have something like what is in the attached photo in addition to what I have asked before like seperate charts.

 

So what you see X in the chart is the sales for the selected date and Y is the sales for the comparison date. In the table below you can see the Date/Date column which explains about the sales difference for the given selected and comparison period for an employee(this would not be required to be displayed though) which would be used to calculate the percentage in the next column.

OwenAuger
Super User
Super User

Hi @thanish

 

One approach would be to have two calendar tables, one with an active relationship to your Sales table and one with an inactive relationship.

 

Sample model here

 

Data model would look like this:

Capture.PNG

Then define measures something like this:

Sales Amount = SUM ( Sales[Sales] )

Sales Amount Compare = 
CALCULATE (
    [Sales Amount],
    ALL ( 'Calendar Select' ),
    USERELATIONSHIP ( Sales[Date], 'Calendar Compare'[Compare Date] )
)

I'm assuming you might want to use both measures in the same expression (to calculate the difference for example) so you would need both to evaluate within the same filter context.

 

I'm not 100% sure that would meet all your needs but hopefully is of some use.

 

Owen 🙂


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!