cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fedde Frequent Visitor
Frequent Visitor

dynamic graphs based on multiple dates

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

1 ACCEPTED SOLUTION

Accepted Solutions
Fedde Frequent Visitor
Frequent Visitor

Re: dynamic graphs based on multiple dates

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.
2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: dynamic graphs based on multiple dates

Hi @Fedde ,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Fedde Frequent Visitor
Frequent Visitor

Re: dynamic graphs based on multiple dates

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,419 guests
Please welcome our newest community members: