I am trying to create a bar chart with sum fact this year versus sum fact for the same period last year, based on a filter date range set by the user.
I was able to get it working using a separate measures table with calculated aggregations, but this meant that the user couldn't filter on any other facts in the data table, without breaking the measures table.
I have taken the calculated measures back into the data table, and am using a contiguous date table. Essentially the table below gives me the data I want for my chart from July 2016 to June 2017; but I can only get it here using the ALLEXCEPT feature, which gives me all periods, not the ones the user has filtered for.
will try the add date again, it's clear the issue i was having was contigious dates however, because when I converted the dates to EOMONTH and joined that to a date table with month end dates it worked. Not the most elegant solution but good enough for now.