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

v-kelly-msft, thanks a lot!