Forum Discussion
Calculate Average based on latest month
Each month, I get an updated forecast for oil production (4.PE - FC17) which includes historical data + forecasted data by month. So for example, May's report will have actual production from Jan - Apr, then forecasted volumes for May - Dec.
Each month, I want to compare the current forecast to the previous month's forecast. To do this, I have one measure that calculates the production from the previous month's forecast and one that calculates production from the current month's forecast. The forecasts are identified using the "PE FC MoYr" column which is just the month-year that the forecast is from. I am using the DAX formula below:
7. Yr PE Curr Mo = CALCULATE('Input Data'[4. PE - FC17], 'Input Data'[PE FC MoYr] = DATE(2017 , MONTH(TODAY()) - 1 , 1))
7. Yr PE Last Mo = CALCULATE('Input Data'[4. PE - FC17], 'Input Data'[PE FC MoYr] = DATE(2017 , MONTH(TODAY()) - 2 , 1))
As you can see, I am using the filter argument in the CALCULATE function to filter on the PE FC MoYr column to pull each individual forecast. The issue I am having is that when we switch into a new month, it throws off the calculations because the formula is looking for the newest month. Once I update with the newest month forecast, all is well, but it's a bit sloppy.
I've tried to do things like make the filter = LASTDATE('Input Data'[PE FC MoYr], but I get en error because I am trying to use a true/false expression in the filter. Is there an easy way around this?
you can use VAR in your measure
Yr PE Curr Mo =
VAR MaxDate = MAX(InputData[PE FC MoYr]) RETURN
CALCULATE('Input Data'[4. PE - FC17], 'Input Data'[PE FC MoYr] = DATE(2017 , MONTH(MaxDate) - 1 , 1))Hi Anonymous,
Please try:
7. Yr PE Curr Mo =
CALCULATE (
'Input Data'[4. PE - FC17],
FILTER (
'Input Data',
'Input Data'[PE FC MoYr] = LASTDATE ( 'Input Data'[PE FC MoYr] )
)
)Regards,
Yuliana Gu
5 Replies
vik0810Resolver V
you can use VAR in your measure
Yr PE Curr Mo =
VAR MaxDate = MAX(InputData[PE FC MoYr]) RETURN
CALCULATE('Input Data'[4. PE - FC17], 'Input Data'[PE FC MoYr] = DATE(2017 , MONTH(MaxDate) - 1 , 1))- vik0810
Hi Anonymous, glad it worked out. The variables defined with VAR can be used in current measure only.
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
Please try:
7. Yr PE Curr Mo =
CALCULATE (
'Input Data'[4. PE - FC17],
FILTER (
'Input Data',
'Input Data'[PE FC MoYr] = LASTDATE ( 'Input Data'[PE FC MoYr] )
)
)Regards,
Yuliana Gu