Forum Discussion
Complex Calendar Table related Question
Hi,
Bit of a complex calendar related table question which I hope someone can help me with.
I have a request to create a line graph showing the number of registrations into a variety of different events over a three-year period. The X axis will display the number of weeks to the date of the event, from the first date a registration was made. The Y axis displaying the total number of registrations.
I have the following events. I want to compare in a graph Event A across 2018,2019,2022. Event B across 2018,2019,2022
Event A – 2018
Event A – 2019
Event A – 2022
Event B – 2018
Event B – 2019
Event B – 2022
Each event cycle is slightly different, examples as follows
I have pivoted the data by date for each event
Any suggestions on how I can ultimately achieve a graph like the below? Hope the above is enough to get a solution, do let me know if any further information is needed. Appreciate it.
This is a general guideline. First I suggest you try and combine your pivoted tables as such:
Date | Event | No Registrations | (Cumulative) 28/05/2017 | Event A 2017 | 12 | (12) I put the cumulative in brackets as this could and should be calculated as a measure.
Then, create a relationship from a table listing the event dates. Registrations opened -date is not needed.
After that you can create a column for "Weeks before event date". Have a look at related() if you want to use a dax column. The calculation (week difference) should be easily googleable.
Please ask help for any stage of this process.Thank you Gabriel_Walkman This is perfect, thank you
4 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523This is a general guideline. First I suggest you try and combine your pivoted tables as such:
Date | Event | No Registrations | (Cumulative) 28/05/2017 | Event A 2017 | 12 | (12) I put the cumulative in brackets as this could and should be calculated as a measure.
Then, create a relationship from a table listing the event dates. Registrations opened -date is not needed.
After that you can create a column for "Weeks before event date". Have a look at related() if you want to use a dax column. The calculation (week difference) should be easily googleable.
Please ask help for any stage of this process.Thank you Gabriel_Walkman This is perfect, thank you
Hi SuperCal99 ,
Sorry for that the information you have provided is not making the problem clear to me.
Can you please share more details to help us clarify your scenario?Or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
