dynamic graphs based on multiple dates

[Anonymous]

Hello,

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.

Any suggestions?
Thanks

[v-yulgu-msft]

Hi @Anonymous ,

To make the requirement to be more clear, please provide some dummy data and show us the expected output.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous · Accepted solution]

I already solved it using the following measures:

SelectedYear = SELECTEDVALUE(DateContracted[CalendarYear];YEAR(NOW()))

And then for every measure for every year I had to create a measure similar to this one:

Margin+1 = CALCULATE([Margin];FILTER(ConsumptionYear;ConsumptionYear[ConsumptionYear]=([SelectedYear]+1)))

The problem I ran into before is trying to use SelectedValue in a calculate and thus not getting a single value.