Matching responses with calendar

[Anonymous]

Hi Community!

I have a table with survey responses and response date as on the screenshot attached. Also, I have a calendar with the predefined reporting periods like on another screenshot (typically bi-weekly, except the first three weeks of Jan 2020). What is the optimal way to match my responses with the calendar table?

One matter to consider is that I cannot edit responses in m-query for Responses table, bcs this query is uploaded from the database that I cannot access.

Thanks.

[v-kelly-msft · Accepted solution]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

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.

[v-kelly-msft]

Hi @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.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft, thanks a lot!

[amitchandak]

@Anonymous 

Not sure if I got it.

But response date seems to have timestamp

Create a new date from it  and join with your date table

response date = [response_date].date

[Anonymous]

Hi @amitchandak,

Dates from Responses table need somehow fit into the bi-weekly periods I have in Calendar table. I mean that responses received on 01 Jan 2020, 08 Jan 2020 and 11 Jan 2020 all belong to the period Week 1-3, etc. And here I cannot get the idea on how to match.

Thanks.