Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |