topic Group by month to average weekly data in DAX Commands and Tips

Group by month to average weekly data

el_astur — Wed, 22 Apr 2020 11:50:38 GMT

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 creating some measures, trying to do some averages by months.

Here you can see distinct users who logged

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

Re: Group by month to average weekly data

Anonymous — Thu, 23 Apr 2020 03:25:42 GMT

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

Re: Group by month to average weekly data

el_astur — Thu, 23 Apr 2020 08:40:18 GMT

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