Forum Discussion
Dynamically calculate Average
Thank you for your reply. I have actually edited the query and transposed the data in a way I get month-year and all the activites from a single query. I am no longer using the Date Query. Instead, I have used group_by to group it based on the activity dates and get the sum for each activities. Next step, I put conditional columns to get the number of sessions occured in a month-year by checking the sum column of that particular activity - i.e. if the sum is > 0 the output in conditional column in 1. Hence I get the number of sessions occured in a month - year. After that, I transposed the query and got columns as month-year and all activities in the row. Then I calculated Average using this Power Query - each List.Average(Record.FieldValues(_ & [Session Type = null])). It did work perfect!
You will need to have a column that is properly formatted with a Date. Currently, your date columns (i.e. Month Year) are suggested not to be in this state. Do you have a Date table? If not, please refer to here for a simple date table: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Once you have established that, you can use the Date field hierarchy to drag the Year and Month properly into your table and that should allow you to get the dynamic outputs you are after.
I hope this helps 🙂
Theo
Thank you, Theo! I have made Date table for it as well before. I tired using that to create the table but the outcome is not that useful. Below is the attached snapshot if it.