Forum Discussion
Problems getting previous month averages
I have one table (Overtime) with Activity_date and Activity_hours. I have a date table (Date) which has a relationship between activity_date and the date column of the date table. I am trying to find the average number of weekend hours.
I've tried a few different approaches, but below is the most recent. It works up to the point of adding the PreviousMonth portion. Once I add that I only get blank. Any help would be appreciated.
I'd use the Date table to modify the date filter context
so e.g. if this is your average:Avg = CALCULATE ( DIVIDE ( SUM ( Overtime[Activity_Hours] ), DISTINCTCOUNT ( 'Overtime'[Activity_Date] ), 0 ), KEEPFILTERS(WEEKDAY ( 'Calendar'[Date], 2 ) > 5) )you can calculate previous month average like this:
Avg Prev Month = CALCULATE( [Avg], PREVIOUSMONTH('Calendar'[Date]) )which calculated the period in reference to the filter context in the Calendar table (here row determines specific month):
you can notice that [Avg Prev Month] is empty on total - that's because there is no specific month reference
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
8 Replies
how many months back do you want to average? do you want to average per month or just to have average umber of hours per weekend day?
having some sample rows from the overtime table in copiable format would help me work faster as well, like this
Column1 Column2 A 1 B 2.5 Right now I'm only looking for the month before the latest date. I have a monthly average with no months specifically filtered that I can plot and break out by month, but when I try to only pick the last month for use in another measure I run into an issue. The data refreshes once per week so typically it will be last month in relation to today with a few expections. Thanks!
This is a sample of the data
Activity_Date Activity_Hours Sunday, June 30, 2019 12 Sunday, June 30, 2019 8 Friday, June 28, 2019 8 Thursday, June 27, 2019 12 Tuesday, June 25, 2019 12 Sunday, July 21, 2019 12 Saturday, July 20, 2019 8 Saturday, July 06, 2019 8 Friday, July 05, 2019 12 Thursday, July 04, 2019 12 Wednesday, July 03, 2019 12 Wednesday, July 03, 2019 8 Tuesday, July 02, 2019 8 Monday, July 01, 2019 12 Sunday, June 30, 2019 12 Saturday, June 29, 2019 12 Friday, June 28, 2019 8 Thursday, June 27, 2019 8 Thursday, June 27, 2019 12 Wednesday, June 26, 2019 12 Tuesday, June 25, 2019 12 Monday, June 24, 2019 8 Tuesday, May 28, 2019 8 Monday, May 27, 2019 12 Sunday, May 26, 2019 12 Sunday, May 26, 2019 8 Saturday, May 25, 2019 8 I'd use the Date table to modify the date filter context
so e.g. if this is your average:Avg = CALCULATE ( DIVIDE ( SUM ( Overtime[Activity_Hours] ), DISTINCTCOUNT ( 'Overtime'[Activity_Date] ), 0 ), KEEPFILTERS(WEEKDAY ( 'Calendar'[Date], 2 ) > 5) )you can calculate previous month average like this:
Avg Prev Month = CALCULATE( [Avg], PREVIOUSMONTH('Calendar'[Date]) )which calculated the period in reference to the filter context in the Calendar table (here row determines specific month):
you can notice that [Avg Prev Month] is empty on total - that's because there is no specific month reference
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
