cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
vik0810
Resolver V
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))

 

 

 

View solution in original post

v-yulgu-msft
Microsoft
Microsoft

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

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.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Microsoft
Microsoft

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

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. 

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors