Daily average per month

Anonymous,

Yes, you would need to create a calendar table,  you can use DAX below and perform the following steps.

1. Create calendar table.

Calender = ADDCOLUMNS (CALENDAR (DATE(2018,1,1), DATE(2018,12,31)),
"DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "MM/YYYY" ),
"YearMonthShort", FORMAT ( [Date], "mmm/YYYY" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

2. Create a holiday table.


3. Create the following columns in the calendar table.

Holiday = RELATED(Holiday[Holiday])

Workday = IF(ISBLANK(Calender[Holiday]),IF(Calender[DayOfWeekNumber]<=5,1,0),0)

4. Create relationship among the three tables as below.


5. Create measure in Calendar table and create average measure in your orginal table.

CountWorkdays = CALCULATE(COUNT(Calender[Workday]),Calender[Workday]=1)

average = COUNT(Table1[date resolved])/[CountWorkdays]

6. Create a table visual, and set average to be not blank. For more details, please review attached PBIX file.


Regards,
Lydia