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 - A...
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
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi 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
The FILTER option didn't work. The formula went through fine, but the filter function itself isn't filtering anything.
Sorry - I accidentally accepted that as the solution but it was the VAR approach that worked.