Calculate Average based on latest month

[Anonymous]

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?

[vik0810 · Accepted solution]

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

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

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. 

[vik0810 · Accepted solution]

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

[Anonymous]

Wow thanks this worked. Not sure I understand the syntax though... 

Does VAR allow you to define a local variable that only works within that measure? Or can I use MaxDate in other measures as well? 

Thanks for the help!

[vik0810]

Hi @Anonymous, glad it worked out. The variables defined with VAR can be used in current measure only.