Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matching responses with calendar

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.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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!