Two dates from the same date column

[thanish]

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

Month	Sales
Jan	1000
Feb	1500
Mar	4100
Apr	466
May	4100
Jun	5615
Jul	5145
Aug	7800
Sep	4830
Oct	5478
Nov	4871
Dec	6874

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

[BhaveshPatel]

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

See the attached screenshot.

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. 

[thanish]

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 ?

[v-yuezhe-msft]

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.

 

Thanks,
Lydia Zhang

[thanish]

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

[thanish]

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]

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:

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 🙂