Complex Calendar Table related Question

[SuperCal99]

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.

[Gabriel_Walkman · Accepted solution]

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.

[Anonymous]

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

[Gabriel_Walkman · Accepted solution]

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.

[SuperCal99 · Accepted solution]

Thank you @Gabriel_Walkman This is perfect, thank you

[lbendlin]

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/1447523