Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-jiascu-msft
Employee
Employee

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.
GilbertQ
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.