cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Daily average per month

I am new to Power BI and am looking for some help.  I am looking to calculate a daily average per person based on # of working days in the month.  My data includes the id, date/time resolved and case#.  So for example I have  id#1 who resolved 30 cases in Sept which had 19 working day for an average of 1.6 and 40 cases in August which had 23 working days for an average of 1.7.  Do I need to create a calendar table?  And what would the formula be?

Thank you

1 ACCEPTED SOLUTION
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements