Forum Discussion
Dynamic Date Shifting for Line chart Comparison
Hello,
I want to compare TY and LY sales based on events.
But the event dates are off by some days, ( All events' dates off are not similar, - For event 1 TY and LY are off by 5 days, but for event 2 TY and LY dates are off by 7 days)
So I am trying to add a date offset slider to fix the problem as I will snapshot individual events.
Below is a picture.
So if I slide by 18 the Light blue Line should move forward with 18 dates and match the peak.
Any leads are appreciated.
Hello Scott,
Thank you for the above information, I have a limitation with this measure, my events do not have a fixed number on the day shift.
So I used this method,1. Created a measure table named Date Shift,
with a columnsDate Shift = GENERATESERIES(-30, 30, 1)
Date Shift Value = SELECTEDVALUE('Date Shift'[Date Shift],0)
2. In the Date table, with the date column create another columnShifted Date = DATEADD('Date Table'[Date],'Date Shift'[Date Shift Value] -371,DAY)
3. In the DollarAmount Table, create Amount TY and Amount LY
Amount LY = CALCULATE(Sum('DollarAmount'[Amount]), DATEADD('Date Table'[Date], 'Date Shift Table'[Date Shift Value] -371 , DAY))4. Create Line Chart,
Add Date as X-axis
Amount TY and Amount LY as Y-axis
Shifted Date from Date Table as Tooltips
5. Add a slicer with a slider and add the Date Shift column from the Date Shift Table as a Field.
Your Line Chart with the Date Shift is ready.
This may not be an ideal solution to the problem, but it worked for me .
2 Replies
Hi Jidnyasa2904 ,
You can try the following expression for date offset:
Measure = DATE(YEAR(max('Table'[Date1])),MONTH(max('Table'[Date1])),DAY(max('Table'[Date1])+Parameter[Parameter Value]))Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hello Scott,
Thank you for the above information, I have a limitation with this measure, my events do not have a fixed number on the day shift.
So I used this method,1. Created a measure table named Date Shift,
with a columnsDate Shift = GENERATESERIES(-30, 30, 1)
Date Shift Value = SELECTEDVALUE('Date Shift'[Date Shift],0)
2. In the Date table, with the date column create another columnShifted Date = DATEADD('Date Table'[Date],'Date Shift'[Date Shift Value] -371,DAY)
3. In the DollarAmount Table, create Amount TY and Amount LY
Amount LY = CALCULATE(Sum('DollarAmount'[Amount]), DATEADD('Date Table'[Date], 'Date Shift Table'[Date Shift Value] -371 , DAY))4. Create Line Chart,
Add Date as X-axis
Amount TY and Amount LY as Y-axis
Shifted Date from Date Table as Tooltips
5. Add a slicer with a slider and add the Date Shift column from the Date Shift Table as a Field.
Your Line Chart with the Date Shift is ready.
This may not be an ideal solution to the problem, but it worked for me .
