Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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....
Hi, @BKnecht
What kind of data do you have now? Could you share a little sample? Maybe there is a solution.
Best regards.
Dale
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |