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.
I have Table like
Date , Dayof week (Sunday, Monday etc), Region(East, West, Central), CallCount
I want to calulate MTD AVG_CallCount by Dayof the week For that region & YTD AVG_CallCount by Dayof the week For that region
Example
02/11/2018 is the 2nd monday of Feb and 7th Monday of the year.
Now I want to calculate AVG call count for all the Monday till 2/11/2018 (including 2/11) for February , 2/5 & 2/11, for MTD
& AVG call count for all the Monday till 2/11/2018 (including 2/11) for 2018 , for YTD.
Date | WeekofDay | Region | CallCount | Avg_MTD_Weekoftheday |
1/1/2018 | Monday | Central | 100 | 100 |
1/1/2018 | Monday | East | 200 | |
1/1/2018 | Monday | West | 300 | |
1/2/2018 | Tuesday | Central | 150 | |
1/2/2018 | Tuesday | East | 250 | |
1/2/2018 | Tuesday | West | 350 | |
1/3/2018 | Wednesday | Central | ||
1/3/2018 | Wednesday | East | ||
1/3/2018 | Wednesday | West | ||
1/4/2018 | Thursday | Central | ||
1/4/2018 | Thrusday | East | ||
1/4/2018 | Thrusday | West | ||
1/5/2018 | Friday | Central | ||
1/5/2018 | Friday | East | ||
1/5/2018 | Friday | West | ||
1/6/2018 | Saturday | Central | ||
1/6/2018 | Saturday | East | ||
1/6/2018 | Saturday | West | ||
1/7/2018 | Sunday | Central | ||
1/7/2018 | Sunday | East | ||
1/7/2018 | Sunday | West | ||
1/8/2018 | Monday | Central | 200 | 150 |
1/8/2018 | Monday | East | 100 | |
1/8/2018 | Monday | West | 50 | |
1/9/2018 | Tuesday | Central | 100 | |
1/9/2018 | Tuesday | East | 200 | 225 |
1/9/2018 | Tuesday | West | 300 |
Solved! Go to Solution.
Hi @purana2002,
Please create two calculated columns via below DAX:
MTD rolling average = CALCULATE ( AVERAGE ( Test_1[CallCount] ), FILTER ( ALLEXCEPT ( Test_1, Test_1[Date].[Month], Test_1[WeekofDay], Test_1[Region] ), Test_1[Date] <= EARLIER ( Test_1[Date] ) && Test_1[Date].[MonthNo] = EARLIER ( Test_1[Date].[MonthNo] ) ) ) YTD rolling average = CALCULATE ( AVERAGE ( Test_1[CallCount] ), FILTER ( ALLEXCEPT ( Test_1, Test_1[WeekofDay], Test_1[Region] ), Test_1[Date] <= EARLIER ( Test_1[Date] ) && Test_1[Date].[Year] = EARLIER ( Test_1[Date].[Year] ) ) )
Best regards,
Yuliana Gu
Hi @purana2002,
Please create two calculated columns via below DAX:
MTD rolling average = CALCULATE ( AVERAGE ( Test_1[CallCount] ), FILTER ( ALLEXCEPT ( Test_1, Test_1[Date].[Month], Test_1[WeekofDay], Test_1[Region] ), Test_1[Date] <= EARLIER ( Test_1[Date] ) && Test_1[Date].[MonthNo] = EARLIER ( Test_1[Date].[MonthNo] ) ) ) YTD rolling average = CALCULATE ( AVERAGE ( Test_1[CallCount] ), FILTER ( ALLEXCEPT ( Test_1, Test_1[WeekofDay], Test_1[Region] ), Test_1[Date] <= EARLIER ( Test_1[Date] ) && Test_1[Date].[Year] = EARLIER ( Test_1[Date].[Year] ) ) )
Best regards,
Yuliana Gu
I have similar data but at Date Level (No rgion on this),
Date | DayofWeek | CallCount | WeekDayMTDRollingAverage | WeekdayYTDRollingAverage |
1/1/2018 | Monday | 12496 | 12496 | 12496 |
1/2/2018 | Tuesday | 19081 | 19081 | 19081 |
1/3/2018 | Wednesday | 16702 | 16702 | 16702 |
1/4/2018 | Thursday | 10334 | 10334 | 10334 |
1/5/2018 | Friday | 15116 | 15116 | 15116 |
1/6/2018 | Saturday | 13487 | 13487 | 13487 |
1/7/2018 | Sunday | 11907 | 11907 | 11907 |
1/8/2018 | Monday | 16516 | 14506 | |
1/9/2018 | Tuesday | 14125 | 16603 | |
1/10/2018 | Wednesday | 12956 | ||
1/11/2018 | Thursday | 11915 | ||
1/12/2018 | Friday | 11756 | ||
1/13/2018 | Saturday | 10437 | ||
1/14/2018 | Sunday | 8997 | ||
1/15/2018 | Monday | 14054 | 14355.33333 | |
1/16/2018 | Tuesday | 12763 | ||
1/17/2018 | Wednesday | 10762 | ||
1/18/2018 | Thursday | 11177 | ||
1/19/2018 | Friday | 10876 | ||
1/20/2018 | Saturday | 8644 | ||
1/21/2018 | Sunday | 6806 | ||
1/22/2018 | Monday | 10969 | ||
1/23/2018 | Tuesday | 9877 | ||
1/24/2018 | Wednesday | 9514 | ||
1/25/2018 | Thursday | 9507 | ||
1/26/2018 | Friday | 9400 | ||
1/27/2018 | Saturday | 7845 | ||
1/28/2018 | Sunday | 6565 | ||
1/29/2018 | Monday | 10009 |
example,
WeekDayMTDRollingAverage = Avg of all the monday before 1/8 including that monday for that month
WeekDayYTDRollingAverage = Avg of all the monday before 1/8 including that monday for that year
1/8/2018 | Monday |
I am using this Dax but its not working , it just giving me MTD avg instead of WeekdayMTDAvg. Am I doing something wrong?
WeekDayMTDRollingAverage =
CALCULATE (
AVERAGE ( ACA_AS_Operational[CallCount] ),
FILTER (
ALLEXCEPT ( ACA_AS_Operational, ACA_AS_Operational[Date].[Month], ACA_AS_Operational[DayOfWeek], ACA_AS_Operational[Region] ),
ACA_AS_Operational[Date] <= EARLIER ( ACA_AS_Operational[Date] )
&& ACA_AS_Operational[Date].[MonthNo] = EARLIER ( ACA_AS_Operational[Date].[MonthNo] )
)
)
WeekdayYTDRollingAverage =
CALCULATE (
AVERAGE ( ACA_AS_Operational[CallCount] ),
FILTER (
ALLEXCEPT ( ACA_AS_Operational, ACA_AS_Operational[Date].[Year] , ACA_AS_Operational[DayOfWeek] ),
ACA_AS_Operational[Date] <= EARLIER ( ACA_AS_Operational[Date] )
&& ACA_AS_Operational[Date].[Year] = EARLIER ( ACA_AS_Operational[Date].[Year] )
)
)
I think it worked, Thank you so much Yuliana,
I have Table like
Date , Dayof week (Sunday, Monday etc), Region(East, West, Central), CallCount
I want to calulate MTD AVG_CallCount by Dayof the week For that region & YTD AVG_CallCount by Dayof the week For that region
Example , MTD monday Avg for 2/11
02/11/2018 is the 2nd monday of Feb and 7th Monday of the year.
Now I want to calculate AVG call count for all the Monday till 2/11/2018 (including 2/11) for February , 2/5 & 2/11, for MTD
& AVG call count for all the Monday till 2/11/2018 (including 2/11) for 2018 , for YTD.
Date | WeekofDay | Region | CallCount | Avg_MTD_Weekoftheday |
1/1/2018 | Monday | Central | 100 | 100 |
1/1/2018 | Monday | East | 200 | |
1/1/2018 | Monday | West | 300 | |
1/2/2018 | Tuesday | Central | 150 | |
1/2/2018 | Tuesday | East | 250 | |
1/2/2018 | Tuesday | West | 350 | |
1/3/2018 | Wednesday | Central | ||
1/3/2018 | Wednesday | East | ||
1/3/2018 | Wednesday | West | ||
1/4/2018 | Thursday | Central | ||
1/4/2018 | Thrusday | East | ||
1/4/2018 | Thrusday | West | ||
1/5/2018 | Friday | Central | ||
1/5/2018 | Friday | East | ||
1/5/2018 | Friday | West | ||
1/6/2018 | Saturday | Central | ||
1/6/2018 | Saturday | East | ||
1/6/2018 | Saturday | West | ||
1/7/2018 | Sunday | Central | ||
1/7/2018 | Sunday | East | ||
1/7/2018 | Sunday | West | ||
1/8/2018 | Monday | Central | 200 | 150 |
1/8/2018 | Monday | East | 100 | |
1/8/2018 | Monday | West | 50 | |
1/9/2018 | Tuesday | Central | 100 | |
1/9/2018 | Tuesday | East | 200 | 225 |
1/9/2018 | Tuesday | West | 300 |
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |