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 (typic...
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!
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.