I would like to be able to show on a table/matrix chart 15 week-endings (7 day periods) starting from a variable date that is selected from a dropdown.
The variable date could start on any day of the week. So each week-ending is 7 days apart, starting from the variable date working backward for 15 weeks.
The attached Excel file shows the end result, where the variable date is selected by the user and is also included at the bottom of the table/chart.
Thanks, @Melissa, I’ll have a look. Can you briefly explain to me what the “Extended Date Table” is as compared to the usual EDNA date table I’ve used for a number of years now, please?
What I am looking for is 15 previous dates starting from the selected date working back in time, with each date 7 days apart. The “Week Ending” column in the “Goal.xslx” file provides an example of the date spread. I don’t think this needs an extra date table, probably just a DAX calculation.
Maybe both…
You might need to tweek it a bit, but this should get you started. I’ve used the disconnected Dates[Date] on the slicer so no filter will not propagate through a relationship and filter your fact table and used the ShowDates measure as a visual level filter…
As for why you should switch to the Extended Date table? It has some nice features that you’ll quickly learn to appreciate - for example Offsets used here. Want to learn more check out @BrianJ 's session during the Business Intelligence Summit later today.
I’ve been trying to get what you’re getting in your screenshot, but it is not quite working out because I can’t see exactly what you’ve done to achieve the screenshot. Can you perhaps send the file?
I previously misunderstood your comment “used the ShowDates measure as a visual level filter”. I’ve used this technique before and it works really great. I’ve got it now though, thanks for that!!
So, should I start using the “Extended Date” in place of the older eDNA date query?
