Average per day by month

[madlfc87]

Hello,

I would like to calculate the average per day for each month for the following measure.

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 tried the below but I believe it averages over all the rows of the month not all the days.

Average Days = AVERAGEX('tblHolidayDates_all locations',[Days Test])

For example I have the below table

With the average calculation above it gives the following results which are not what I want.

 What I need for example is for January Average to be 53 / 31(the number of days in January) = 1.70

Link to my pbix file.

https://drive.google.com/file/d/1XCdCxwjUYtOseZMJ7AUa_HXLsidposzr/view?usp=sharing

Appreciate your help.

Thank you

[Ashish_Mathur]

Hi,

Revise the measure to

Days Test = DIVIDE(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]),COUNTROWS('Calendar'))

Hope this helps.

[lbendlin]

but I believe it averages over all the rows of the month not all the days.

Add an intermediate step with SUMMARIZE or SUMMARIZECOLUMNS that pre-aggregates your data by day.

[madlfc87]

Thank you for your reply. Could you be a bit more specific please?

[lbendlin]

Please provide a smaller sample file. Yours is way too big.

[madlfc87]

@lbendlin 

The below is a screenshot of the table tblHolidayDates_all locations

and the below is a screenshot of the table Calendar

Appreciate your help

[lbendlin]

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.

[madlfc87]

This is tblHolidayDates_all locations

https://docs.google.com/spreadsheets/d/1oMMfpBywHQkPqwIhLZs-eCLLqw4LbPSy/edit?usp=sharing&ouid=11352...

This is Calendar

https://docs.google.com/spreadsheets/d/1Lp4bKhQqtMXo1GKb7AlWARMz2p6FLYrS/edit?usp=sharing&ouid=11352...

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

[lbendlin · Accepted solution]

I will ignore these first couple rows?