cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Group by month to average weekly data

Hi all. I'll try to explain it as best i can.
I have something like this

pUYakvK

Not a big deal, its a calendar with day, month, week. (a week can belong to different months)

Problem comes when creating some measures, trying to do some averages by months.

Here you can see distinct users who logged

M1Kb4xU

week 09 is present in both february an march, so when making monthly average, depending on its first day, average measure is a mess because getting data from 1 or 2 days for a week.
Week 9 should be 2579, so when trying to get average from march what should be over 2500 drops to 2000

(Weeks from March with seven days: 2785 + 2588 + 2538 + 2202 ->AVG >2500)
What happens 2785 + 2588 + 2538 + 2202 + (501+ 1599) - >AVG <2050

I would have no problem taking into account the entire week (even only one day belongs that month) or taking only weeks with 7 days,( average would be closer to the truth) but i dont know how to do it easily without losing filters.
Maybe trying to count days in that week,some sort of weighting with them, somehow ignore duplicated ones ... any idea is welcomed.

Thx in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Group by month to average weekly data

Weeks are best handled as ISO weeks. Dealing with the usual weeks is messy because they do not go evenly into months and quarters and years.

 

Here's a measure that returns a weekly average considering only the full 7-day weeks contained in the current period of time.

 

 

	[Weekly Avg (only full 7 days)] =
		AVERAGEX(
			FILTER(
				// WeekId should uniquely identify
				// weeks in the calendar.
				VALUES( Calendar[WeekId] ),
				CALCULATE(
					COUNTROWS( 'Calendar' ) = 7
				)
			),
			[Your Measure]
		)

 

 

Best
D

View solution in original post

2 REPLIES 2
Highlighted
Anonymous
Not applicable

Re: Group by month to average weekly data

Weeks are best handled as ISO weeks. Dealing with the usual weeks is messy because they do not go evenly into months and quarters and years.

 

Here's a measure that returns a weekly average considering only the full 7-day weeks contained in the current period of time.

 

 

	[Weekly Avg (only full 7 days)] =
		AVERAGEX(
			FILTER(
				// WeekId should uniquely identify
				// weeks in the calendar.
				VALUES( Calendar[WeekId] ),
				CALCULATE(
					COUNTROWS( 'Calendar' ) = 7
				)
			),
			[Your Measure]
		)

 

 

Best
D

View solution in original post

Highlighted
Frequent Visitor

Re: Group by month to average weekly data

thx for the help.
Testing things out i got a solution that i think is enough for me.
I made a calculated column with the enddate for that week, and another one for a "Virtual" month, something like this

FORMAT (
    IF (
        DAY ( Calendar[EndWeek] ) < 4;
        IF (
            MONTH ( Calendario[EndWeek] ) < 2;
            DATE ( Calendar[EndWeek].[Year] - 1121 );
            DATE ( Calendar[Date].[Year]; MONTH ( Calendar[EndWeek] ) - 11 )
        );
        DATE ( Calendar[Date].[Year]; MONTH ( Calendario[EndWeek] )1 )
    );
    "mmmm"
)

I check the end of the week so if the first week of the month has 3 days or less that week goes to the previous month (takinginto account January). So weeks belonging to two diferent months go to the Virtual month with >= 4 days.

I'll try your solution too. Thanks

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (782)