Forum Discussion

el_astur's avatar
el_astur
Frequent Visitor
6 years ago
Solved

Group by month to average weekly data

Hi all. I'll try to explain it as best i can. I have something like this Not a big deal, its a calendar with day, month, week. (a week can belong to different months) Problem comes when crea...
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • el_astur's avatar
    el_astur
    Frequent Visitor
    6 years ago

    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] - 1; 12; 1 );
                DATE ( Calendar[Date].[Year]; MONTH ( Calendar[EndWeek] ) - 1; 1 )
            );
            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