Forum Discussion
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
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
1 Reply
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