Struggling with MAX

[ecfrench]

I apologize in advance for the length of this post but the background is necessary to fully frame the problem I’m having.

I am analyzing a data file containing monthly forecasted costs updated after actual costs are posted on a monthly basis.  The data file (“All Forecasts”) contains a version number field that contains a system-generated unique identifier ('Forecast Version Number') for each monthly version of the forecast:

The analysis I’m trying to do compares the current forecast (extracted from “All Forecasts” into a calculated table based on the “current” value in the ‘Forecast Version Type’ column) with the last forecast.  I create another calculated table called ‘Historic Forecasts’ by extracting all the values from “All Forecasts” that don’t say “current” in the ‘Forecast Version Type’ column.  I then want to subtract the last forecast from the current forecast to calculate how much the forecast changed from last month.  My logic is that the highest forecast version number in the ‘Historic Forecast’ table is the last forecast.  This seems to work fine as long as none of the previous forecast versions have forecasted costs in more months than the current forecast version.  See the table below:

The ‘Last Forecast’ column calculates the last forecast using the following formula:

Last Forecast =

VAR MaxVersionNo = MAX('Historic Forecast Table'[No])

RETURN

CALCULATE( SUM('Historic Forecast Table'[Forecast]),

  FILTER('Historic Forecast Table', 'Historic Forecast Table'[No]=MaxVersionNo)

)

Where ‘Historic Forecast Table’[No] = Forecast Version Number

The ‘Remaining Last Forecast’ column calculates the forecasts for the months after that last date of actuals from the previous period.  For example, if the current forecast was created in September and includes the forecasted costs for September on, the ‘Remaining Last Forecast’ would show what the monthly forecasted costs were for August moving forward.

What you’ll see is the Current Forecast (‘Actuals and Forecast’) stops when the actuals stop.  This is because there are no forecasted costs beyond May in the Current Forecast (Version 6).  The problem is, there were also no forecasted costs beyond May in the Last Forecast (Version 5).  However, there were forecasted costs beyond May in Version 4, which is what is showing up in the table.

That is my problem.  It seems like my MAX formula in the [Last Forecast] measure is not calculating the global max version; it seems to be looking at each date in the ‘Forecast Period – Year Month’ column (in the first table), calculating the MAX Version No for each month, and then returning the forecasted value associated with that MAX Version No.  I only want it to return a value associated with the global max, i.e. Version 5.  Therefore, if Version 4 has a forecast value in June but Version 5 does not, the measure should return nothing for June.

Thanks for sticking through to the end and I appreciate any advice.

[ecfrench]

I apologize in advance for the length of this post but the background is necessary to fully frame the problem I’m having.

I am analyzing a data file containing monthly forecasted costs updated after actual costs are posted on a monthly basis.  The data file (“All Forecasts”) contains a version number field that contains a system-generated unique identifier (Forecast Version Number) for each monthly version of the forecast:

The analysis I’m trying to do compares the current forecast (extracted from “All Forecasts” into a calculated table based on the “current” value in the ‘Forecast Version Type’ column) with the last forecast.  I create another calculated table called ‘Historic Forecasts’ by extracting all the values from “All Forecasts” that don’t say “current” in the ‘Forecast Version Type’ column.  I then want to subtract the last forecast from the current forecast to calculate how much the forecast changed from last month.  My logic is that the highest forecast version number in the ‘Historic Forecast’ table is the last forecast.  This seems to work fine as long as none of the previous forecast versions have forecasted costs in more months than the current forecast version.  See the table below:

The ‘Last Forecast’ column calculates the last forecast using the following formula:

Last Forecast =

VAR MaxVersionNo = MAX('Historic Forecast Table'[No])

RETURN

CALCULATE( SUM('Historic Forecast Table'[Forecast]),

  FILTER('Historic Forecast Table', 'Historic Forecast Table'[No]=MaxVersionNo)

)

Where ‘Historic Forecast Table’[No] = Forecast Version Number

The ‘Remaining Last Forecast’ column calculates the forecasts for the months after that last date of actuals from the previous period.  For example, if the current forecast was created in September and includes the forecasted costs for September on, the ‘Remaining Last Forecast’ would show what the monthly forecasted costs were for August moving forward.

What you’ll see is the Current Forecast (‘Actuals and Forecast’) stops when the actuals stop.  This is because there are no forecasted costs beyond May in the Current Forecast (Version 6).  The problem is, there were also no forecasted costs beyond May in the Last Forecast (Version 5).  However, there were forecasted costs beyond May in Version 4, which is what is showing up in the table.

That is my problem.  It seems like my MAX formula in the [Last Forecast] measure is not calculating the global max version; it seems to be looking at each date in the ‘Forecast Period – Year Month’ column (in the first table), calculating the MAX Version No for each month, and then returning the forecasted value associated with that MAX Version No.  I only want it to return a value associated with the global max, i.e. Version 5.  Therefore, if Version 4 has a forecast value in June but Version 5 does not, the measure should return nothing for June.

Thanks for sticking through to the end and I appreciate any advice.

[lbendlin]

Read about context transition and filter modifiers. Most likely your filter context is restricted to the "current row".

A single ALLSELECTED() placed into your CALCULATE formula may already be sufficient.

[ecfrench]

Thanks for the reply @lbendlin.  I had already tried putting ALL or ALLSELECTED both before the FILTER function and after in the CALCULATE function but the result was no different.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[v-yueyunzh-msft]

Hi, @ecfrench 

According to your description, you want to get the global maximum version number. Right?

The defined variable "VAR MaxVersionNo = MAX('Historic Forecast Table'[No])" in your metric is calculated in the current filter context.

You can try to this dax to get the maximum No of the entire table :

VAR MaxVersionNo = MAXX(ALL('Historic Forecast Table'[No]),'Historic Forecast Table'[No])

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ecfrench]

@v-yueyunzh-msft I tried what you suggested but it returned nothing in the last forecast column(s) of the second table.  I think it did that because it truly looked at the global max forecast version in the table, which was the highest version for all projects (something like 13 I think).  I think what I need to do is ignore the date filter context but not the project filter context.  I want to get the max forecast version for the subject project, not the max forecast version for all the projects.

I'm working on a sanitized .pbix file and will upload it when I can.

[v-yueyunzh-msft · Accepted solution]

Hi, @ecfrench 

According to your description, you want to calculate the max version number  for the subject project, not the max forecast version for all the projects.

You can try to this dax to get the maximum No of the subject project :

CALCULATE(MAX('Historic Forecast Table'[No]),ALL('Historic Forecast Table'),VALUES([ProjectNumber]))

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ecfrench]

That worked.  Thank you.