Forum Discussion
Matching responses with calendar
Hi Anonymous ，
First create a column in table "Trigger_quest" to get the date of the column Response_Date,using below dax expression:
Column = DATE(YEAR('Table (2)'[Date]),MONTH('Table (2)'[Date]),DAY('Table (2)'[Date]))Then create a calculated column as below:
Column 2 = CALCULATE(MAX('Table'[Week]),FILTER('Table','Table (2)'[Column]>='Table'[Start date]&&'Table (2)'[Column]<='Table'[End date]))Finally you can use week period to create relationships between the 2 tables.
Here is a sample .pbix file you can refer to.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi v-kelly-msft,
This is super cool, your solution is working! The only thing is that when I sorted my column Period title by Period # there was an issue with the calculated column 2. To overcome this I've created a duplicate column with bi-weekly periods in calendar table and sorted it by Period #.
Also, can you please advise on how do I modidy the syntax to add year to my bi-weekly periods in Trigger_quest table? My survey is going to continue in 2021 onwards, that's why I will modify my calendar table by adding year to period titles (like Weeks 1-3 '20, Weeks 3-6 '20).
Thanks.
Community SupportHi Anonymous ,
For requirement 1:
-Sort Column Period title:
You can go to query editor >add an index column>then sort by index column :
For requirement 2:
You can create a calculated column to achieve it:
column = 'Table'[Week]&" '"&LEFT(YEAR('Table'[Start date]),2)
Finally you will see:
For the related .pbix file,pls click here.
Kelly
v-kelly-msft, thanks a lot!