Forum Discussion
Average per day by month
I will ignore these first couple rows?
The below is a screenshot of the table tblHolidayDates_all locations
and the below is a screenshot of the table Calendar
Appreciate your help
Touche. I should have specified that I cannot work with screenshots. Please provide sample data that fully covers your issue but not more, in a usable format.
Please show the expected outcome based on the sample data you provided.
This is tblHolidayDates_all locations
This is Calendar
This calculates the days in every month
Days Test = COUNTX(ADDCOLUMNS(GENERATE('tblHolidayDates_all locations',DATESBETWEEN('Calendar'[Calendar Date],'tblHolidayDates_all locations'[StartDate],'tblHolidayDates_all locations'[EndDate])),"period",MAXX(FILTER('Calendar','Calendar'[Calendar Date]=EARLIER('Calendar'[Calendar Date])),'Calendar'[Year])),[period])
I want the average of Days Test per day by month. For example if the output for the days is
I would like for January the Site Service average to be 53/31 = 1.70, February 64/28=2.29 and so on. If I do Average Days = AVERAGEX('tblHolidayDates_all locations',[Days Test]) it gives me a different result which I believe it averages the days over how many rows are in the specific month not the total days of the month.
Thank you
I will ignore these first couple rows?