Survey Results with Overlapping MTD, QTD and YTD columns

[WalterBuck]

Hello,

I am trying to design an automated report for our HR team to display their new hire survey results. They send a 3 day, a 45 day and 120 day survey and get the results in a normal excel file. They want a model to display the results of the survey (1-5 on several questions for each) for responses in the last month to date (MTD), quarter to date (QTD), and year to date (YTD). So the main issue is that this data of course is overlapping (a survey reponse that is in the last MTD is also QTD and YTD). So I need it to calculate the averages as such. Currently I have three columns in each table calculating whether the survey response MTD, QTD and YTD. Then I use an averagex(filter( to do the average.  Between the many questions * 3 survey periods it will be a lot of dax measures. Not to mention they want statistics calculated on # of surveys sent (based on sheet with hire date of employees) and # recieved. That will be a whole many more columns and measure to try and calculate and split.

Is there an easier way to do this? Perhaps with a calendar table but not sure of the method. I can continue with my current method but feel like I am missing an easier more effective method. Normally I would define a column that tells you whether response is YTD, QTD, MTD and stick this in the column part of my visual and the average metrics in the values but this won't work since the data is overlapping. Hope this made sense.

[Anonymous]

Hi @WalterBuck 

I've understood your needs currently, but i don't know how your to write the measures and columns, so I can't tell you how to optimize them.

If possible, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.