Forum Discussion
Problems getting previous month averages
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!
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 |
Community ChampionI'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
Proud to be a Datanaut!
It fixed a lot of it but there is still one small part. Thank you for your help so far. When I create a table visual it shows that it works. Now I'm using this information as an input into a projection. I'm adding a column in the date table to project based on last month's numbers, what the rest of the month looks like but when I use the measures for that, I get numbers that don't match the table visualization. I verified independantly in excel that the visual values are correct.
Forecasted = if('Date'[Dateswithdata]=false,if('Date'[Weekend?]=true,[Last Month Average Monthly Weekend Day Hours],[Last Month Average Monthly Weekday Hours]),BLANK())Correct Table Visual:
Column (forecasted) with incorrect numbers, using the same measure:
Thank you for the information so far. Also, for my own knowledge, can you explain why I needed to use the keepfilters function in the measure?
- Stachu
regarding KEEPFILTERS - as we use Calendar[Date] as a second argument for CALCULATE the filter context of that column is overwritten to show only weekends - all the weekends. Now we don't won't to show all of them, only the ones that match current filter context for month - that's why I added KEEPFILTERS
you can read more on CALCULATE here
https://www.sqlbi.com/articles/filter-arguments-in-calculate/the filter context of Date in calculated column is the value in the row, so it calculates in reference to a single date. Other than that adding fact data to a dimension table is not a very elegant solution. Do you need forecast in the calculated column, or could it be in the measure or calculated table? How do you need to use it later? the values should be the same (avg for latest month with actual data) for all the future periods, correct?
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Below is the graphic I'm trying to create, where the projected is using last month's average # of hours per weekday and weekend to project to the end of the month. Right now the projection is being calculated by the measure 'Projected" (below) which uses the calculated column in the dates table in the picture in my earlier post (Date[Forecast]) but like I said, it is not actually relaying the true previous month average. All the help so far is really appreciated. If you have any insight into how better to do this would be appreciated too. Thanks!
Projected = if(SELECTEDVALUE('Date'[Dateswithdata])=FALSE(),calculate(sum(Overtime[Activity_Hours])+(sum('Date'[Forecasted])),DATESMTD('Date'[Date]),year('Date'[Date])>=year(TODAY()),month('Date'[Date])=month(today())),BLANK())