cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BKnecht Helper II
Helper II

Dynamic YoY "Days-Away" Comparison

I'm trying to create measures and a chart but am having some difficulty. For some background, our company holds events each year, and the same event will fall on a different date one year compared to the next (i.e. in 2016, the event is on May 23rd, and in 2017 it's on May 16th).

 

Ultimately, I want a comparison that shows how many registrants we have this year compared to last year, by how many days away from the event we are. For example, the event is on May 16th this year, so I'm 10 days away from the event and have XYZ number of registrants. Last Year, how many registrants did we have when we were 10 days away from the event (i.e how many registrants did we have by May 13th of last year)?

 

Ideally I'd like to show this on a bar chart, but am having trouble with both writing the measure and understanding how to structure the date table so that I can plot these 2 values on a single X axis.

 

Anyone have any ideas? I've been struggling with this for days.... 

2 REPLIES 2
Super User IV
Super User IV

Re: Dynamic YoY "Days-Away" Comparison

Hi @BKnecht

 

What I would do is to possibly create a column in the Query Editor which would be based on how many days out the data is.

 

So for example if it was a week out for the 2016 event it would be 7 days minus 23 May 2016.

And if it was for this years event 2017, it would 7 days minus 13 May 2017

 

In order to do this, you could create some conditional columns in which you define based on the year which Date colum to use.

 

Then you can create the Duration Column with this syntax below

Duration.Days(Duration.From([End]-[Start]))) 

And then you can use this Duration column to show relative to each event how many registrations there were. You can do this by putting the Duration into the Axis, and then putting your measure into the values field. And then finally putting the year into the Legend to show how each year is tracking.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Microsoft
Microsoft

Re: Dynamic YoY "Days-Away" Comparison

Hi, @BKnecht

 

What kind of data do you have now? Could you share a little sample? Maybe there is a solution.

 

 

Best regards.

Dale

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors