Graphing two date slicer selections in one graph with altered dates

[almckenz]

I am trying to use two date slicer selections similar to how it is done in this blog https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/ . All that is fine and well and has been working for some time...

The plot thickens because what I am trying to do on top of that is graph every date in those two selections on a line chart but the second selected date range needs to be adjusted so that it is on top of the first selected date.  The x axis will show the date selected by the first slicer. The date for the second slicer will appear incorrectly, but it needs to appear correctly in the tooltip. 

This image shows two separate selected periods, but I want the first date of the black one to appear like it is the same as the first day as the blue one. The tooltip should have the correct date the black one starts on. 

[almckenz]

@Seward12533 that is super close! Thank you so much. The only thing I cant quite get to work is the tooltip showing the correct unadjusted date. I will play around with it for a while longer, but alas if I cant get that to work right we will need to go the custom visual route. 

[Seward12533]

Actually its very easy.  I could not find a way to start it at Day Zero vs the Index of the Day but other than that I think it came out pretty well.  Don't forget you can use the drill down icons to aggegate by Month,  Week or Day.  And while I averageed the points you can do any aggregate you want (Max, Min, etc..)

Simple way is to just drag the Range1 [Date] and Range 2[Date] fields into the tooltip.  Much cooler is to use a report page tool tip. For this I had to write a measure to calculate the Start dates which was easy since we already had the difference beetween the two start dates as a measure.

MinDate 1 = Var MinDate = MIN(Dates[Date]) RETURN IF([Range 1 Start]>=[Range 2 Start],MinDate-[ShiftDeltaStartDates],MinDate)

MinDate 2 = Var MinDate = MIN(Dates[Date]) RETURN IF([Range 1 Start]<=[Range 2 Start],MinDate+[ShiftDeltaStartDates],MinDate)

Also found a bug in a sign in one of my meaures - fixed in this version if you download again along with tool tip demonstration. To remvoe the page tool tip you need to change the Tool Tip Property for the slicer back to default from Page.

standard tooltipreport page tooltip

[almckenz]

@Seward12533 you are amazing. I am obsessed with the report page tooltip! Thank you so much!

[Seward12533]

Your most welcome and thanks for the Kudo's!! The report page tooltips are one my favorite new features.

[Seward12533]

PS : dont forget to mark as soluiton and probably worth at least a Kudo's or two  since I put a few hours into this. But it was an interesting challenge and I'm sure I'll be able to apply this somewhere eventually.

[almckenz]

@Seward12533 I do have a file that I have mocked up with dummy data, but what is the best way for me to post that? I cannot attach files here which is annoying.  The date data comes from two tables. It is similar enough to the file on sqlbi blog above and that can be downloaded from that link, but I am happy to post a sample file with my best attempt... 

[Seward12533 · Accepted solution]

@almckenz to post a sample you need to upload to a public file sharing site like onedrive, box, drive or whatever and share a link.  I ended up mocking up some data. My initial solution suggestion using calcualted columns didnt' work so I needed to implement with measures. Im sure there is another solution with RANK and earlier or somthing but I have not mastered those functions.  Sorry if I ws cranky but ended putting a few hours into this and while I'm sure you have been pulling your hair out its not clear what you tried and if I endeded up duplicating efforts. It was an interesting problem tho so thank you. 

My solution uses a date table to link the two tables and shifts the dates using DATEADD with an offset equal to the Delta in the start dates.  The tricky part was the dynamic start and end dates for each table and having to test which one comes first and then adapting.   The calculations are done on a daily level and I added indexes for DAY, Week and Month to display arbitrary index on axis vs a date and allow aggegration at the Weekly and Monthly Levels.  The hardest part was figuring out how to calculate the shiifts and get the signs right. 

Note, I used disconnected slicers build dymaically from the two fact tables to avoid the complicatons of needing to use ALL all over the place to remove filters if you tried to slice directly on the fact tables. I then wrote measures to harvest the selected values that I used in my measures. 

Here is a link to my solutuion : https://1drv.ms/u/s!AuCIkLeqFmlhhJkIcpsdkWzMMqeBBw I test a few scenarios and I belive it will work for any arbitrary set of dates. But please test it out. 

Here is a sample of the DAX for Average of the first table.

Avg 1 Shifted = IF([Range 1 Start]<=[Range 2 Start],
    // If the earliest date is from range 1 just use range 1 values but limit to the selected rante
   IF([Range 1 Start]<=MAX(Dates[Date])&&[Range 1 End]>=MAX(Dates[Date]),AVERAGE('Range 1'[Value])),  
   // ELSE imit to the number of selected dates in range 1 and then calculate for Range 1 but shift dates back by the difference in the start dates 
   IF([Range 2 Start]<=MAX(Dates[Date])&&[Range 2 Start]+[Num Days Selected 1]-1>=MAX(Dates[Date]),CALCULATE(AVERAGE('Range 1'[Value]),DATEADD(Dates[Date],-[ShiftDeltaStartDates],DAY))))

 

[Shloka_Lakhani]

Hello @Seward12533,

This reply is useful for my concern . please should you give it from scratch means how to make measure for Range 1 start and Range 2 start.


I have one calendar table with date from 01/01/2020 to till today and one sales table.

Need comparison graph like this when we select dates from two different date filters.

Your response is very valuable for us.
Thank you in advance.  

[keshal_shah]

While clicking on the link "https://1drv.ms/u/s!AuCIkLeqFmlhhJkIcpsdkWzMMqeBBw", it seems to be not available.
@Seward12533  Can you please reshare the link.

[almckenz]

@v-danhe-msft @Seward12533 Thanks for your replies, but I believe you both misunderstood. The PBIX in the link above is similar enough, but alas I cannot attach a PBIX file directly here but this image represents the idea. I want to select date 1 and date 2. Date 1 will set the x axis. So, the x axis will start with 8/28/2016. Date 2, although it does not start at the same date, will appear like it does. in that it will line up with 8/28/2016. The tooltip will say that the date for Date 2 is 2/18/2017. Let me know if you have any thoughts on this. I am rather stumped. 

[Seward12533]

The solution I gave you for relative weeks is very close to what your looking for. In general people are here to help you but really help if you do some of the legwork. Much easier if you post some representative data that represents your best effort. Should not be too hard to mock something up. Are the date ranges from the same table or two different ones. I’ll take a crack at something.

[v-danhe-msft]

Hi @almckenz,

From your description, I could not understand what kind of visual you want to get, do you want to combine the two(black and blue) lines in one visual and filtered by one slicer? If so, you could refer to below steps:

Sample data:

Create a new table and create relationships.

Table = CALENDAR("2018/7/1","2018/7/5")

Create a Line chart visual and a Slicer visual and you could get the result.

If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?

Regards,

Daniel He

[Seward12533]

@almckenz there are a number of ways to do this.  One woudl be to add a calculated column that calculats the number of week since the first date there is data using DATEDIFF. To do this

Measures to calculate the earliest dates in each of your fact tables

EarliestDate1:=CALCULATE(FIRSTDATE(Table1[DATE]),ALL(Table1))
EarliestDate2:=CALCULATE(FIRSTDATE(Table2[DATE]),ALL(Table2))

Calculated columns in the respective FACT tables

Relative Week = = DATEDIFF([EarliestDate1],[DATE],WEEK)

DAX to create a bridge table to bridge the two tables by Relative Weeks
Week = GENERATESERIES(0, DATEDIFF(EarisetDAte1],CALCULATE(LASTDATE(Table2),All(Table2)), 1),WEEK)  // need to do on the larger of your two tables

Then relate your two FACT tabels to the Relative Week Bridge Table and plot your two measures with teh Week Number from the bridge table on the axis.  If you want to do this by Day repalce the WEEK term in the DATE Diff by DAY