Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
purana2002
Helper I
Helper I

MTD and YTD Rolling Average by Day of week (Monday, Tuesday ETC)

‎01-24-2018 11:31 AM

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.

 

DateWeekofDayRegionCallCountAvg_MTD_Weekoftheday
1/1/2018MondayCentral100100
1/1/2018MondayEast200 
1/1/2018MondayWest300 
1/2/2018TuesdayCentral150 
1/2/2018TuesdayEast250 
1/2/2018TuesdayWest350 
1/3/2018WednesdayCentral  
1/3/2018WednesdayEast  
1/3/2018WednesdayWest  
1/4/2018ThursdayCentral  
1/4/2018ThrusdayEast  
1/4/2018ThrusdayWest  
1/5/2018FridayCentral  
1/5/2018FridayEast  
1/5/2018FridayWest  
1/6/2018SaturdayCentral  
1/6/2018SaturdayEast  
1/6/2018SaturdayWest  
1/7/2018SundayCentral  
1/7/2018SundayEast  
1/7/2018SundayWest  
1/8/2018MondayCentral200150
1/8/2018MondayEast100 
1/8/2018MondayWest50 
1/9/2018TuesdayCentral100 
1/9/2018TuesdayEast200225
1/9/2018TuesdayWest300 

 

 

Message 1 of 5
2,270 Views
0
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

‎01-26-2018 10:01 PM

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] )
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 3 of 5
2,570 Views
4 REPLIES 4
v-yulgu-msft
Employee
Employee

‎01-26-2018 10:01 PM

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] )
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 3 of 5
2,571 Views
purana2002
Helper I
Helper I
In response to v-yulgu-msft

‎02-09-2018 10:30 AM

I have similar data but at Date Level (No rgion on this),

 

DateDayofWeekCallCountWeekDayMTDRollingAverageWeekdayYTDRollingAverage
1/1/2018Monday124961249612496
1/2/2018Tuesday190811908119081
1/3/2018Wednesday167021670216702
1/4/2018Thursday103341033410334
1/5/2018Friday151161511615116
1/6/2018Saturday134871348713487
1/7/2018Sunday119071190711907
1/8/2018Monday1651614506 
1/9/2018Tuesday1412516603 
1/10/2018Wednesday12956  
1/11/2018Thursday11915  
1/12/2018Friday11756  
1/13/2018Saturday10437  
1/14/2018Sunday8997  
1/15/2018Monday1405414355.33333 
1/16/2018Tuesday12763  
1/17/2018Wednesday10762  
1/18/2018Thursday11177  
1/19/2018Friday10876  
1/20/2018Saturday8644  
1/21/2018Sunday6806  
1/22/2018Monday10969  
1/23/2018Tuesday9877  
1/24/2018Wednesday9514  
1/25/2018Thursday9507  
1/26/2018Friday9400  
1/27/2018Saturday7845  
1/28/2018Sunday6565  
1/29/2018Monday10009  

 

 

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/2018Monday

 

 

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] )
)
)

Message 5 of 5
2,191 Views
0
purana2002
Helper I
Helper I
In response to v-yulgu-msft

‎01-27-2018 05:35 AM

I think it worked, Thank you so much Yuliana,

Message 4 of 5
2,242 Views
0
purana2002
Helper I
Helper I

‎01-24-2018 11:34 AM

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.

 

DateWeekofDayRegionCallCountAvg_MTD_Weekoftheday
1/1/2018MondayCentral100100
1/1/2018MondayEast200 
1/1/2018MondayWest300 
1/2/2018TuesdayCentral150 
1/2/2018TuesdayEast250 
1/2/2018TuesdayWest350 
1/3/2018WednesdayCentral  
1/3/2018WednesdayEast  
1/3/2018WednesdayWest  
1/4/2018ThursdayCentral  
1/4/2018ThrusdayEast  
1/4/2018ThrusdayWest  
1/5/2018FridayCentral  
1/5/2018FridayEast  
1/5/2018FridayWest  
1/6/2018SaturdayCentral  
1/6/2018SaturdayEast  
1/6/2018SaturdayWest  
1/7/2018SundayCentral  
1/7/2018SundayEast  
1/7/2018SundayWest  
1/8/2018MondayCentral200150
1/8/2018MondayEast100 
1/8/2018MondayWest50 
1/9/2018TuesdayCentral100 
1/9/2018TuesdayEast200225
1/9/2018TuesdayWest300 

 

 

Message 2 of 5
2,276 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All