Currently I'm creating a Dashboard representing sales. The user gets to chose the year in which the sales are contracted. I want to have graphs that show the sales for when the product is actually sold (this can be any year in the after the contract has been signed). This currently works fine.
However ideally I would like to update the graphs automatically to the correct consumptionYear based on a slicer for contractedYear. e.g. User picks the the contractedYear 2018, the graph should then automatically display the next year for consumptionYear.
There is a 1:N-relationship as consumptionYear just holds the years and contractedYears holds the exact days. Power BI does not offer a relative filter option against another date (this would be a perfect solution).
I've tried several solutions, I've spent most time trying to create a calculated column relativeYear. But I think this is calculated before the user selected slicer is applied and therefore not possible (before the user selects a contractedYear both date tables have multiple dates).
I have a measure showing the difference in years (using LastNonblank for both dates) which seems to work. Another option create different measures for each year for the measures which I'm trying to show in the graph. But I also couldn't get this to work.