cancel
Showing results for
Did you mean: Anonymous
Not applicable

## 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?

2 ACCEPTED SOLUTIONS  Resolver 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))
```  Microsoft

Hi @Anonymous,

`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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5  Microsoft

Hi @Anonymous,

`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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Anonymous
Not applicable

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.  Resolver 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))
``` Anonymous
Not applicable

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!  Resolver V

Hi @Anonymous, glad it worked out. The variables defined with VAR can be used in current measure only. Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  