get the measure value for the latest forecasted date values in a table

[Anonymous]

Hello All,

Need help with a requirement  where I need to pick up the Empties_Forecast values for Max( Forecasted_on ) 
Also,the grouping should be happen at Forecast_daydate, Returnal_Group, Brewery, Location.
please find the picture with dummy values and underlined the required values.
 

Thanks in Advance,

Raki

[Anonymous]

Hi @Anonymous ,

Please have a try.

Create a measure.

CALCULATE(MAX(Empties_Forecast),FILTER(ALL(TABLE), Brewery=SELECTEDVALUE(Brewery)&&Location=SELECTEDVALUE(Location))))
If it does't meet your expected output,please provide some data and desired output.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi @Anonymous ,
THank you so much for the response.

Please find the data in the below path for the data.

https://docs.google.com/spreadsheets/d/1I4TeWNTERa4jMY7x79HvV1N57if597rw/edit?usp=sharing&ouid=100834257689206560977&rtpof=true&sd=true 

The result should be like for each: Forecast_daydate--location--brewery--Returnable_Group I need to find out the  Empties_Forecast for either max(Forecasted_on) or Forecast_Weekdate = Forecasted_on. Both  the logics will result the same output value.

Thanks in advance,

Raki

[Anonymous]

Hi @Anonymous 

Please have a try.

measure_IF = var max_date = CALCULATE(MAX('Table'[Forecasted_On]),FILTER(ALL('Table'),'Table'[Forecast_DayDate]=SELECTEDVALUE('Table'[Forecast_DayDate])&&'Table'[Returnable_Group]=SELECTEDVALUE('Table'[Returnable_Group])&&'Table'[Brewery]=SELECTEDVALUE('Table'[Brewery])&&'Table'[location]=SELECTEDVALUE('Table'[location])))
var value_ = CALCULATE(MAX('Table'[Empties_Forecast]),FILTER(ALL('Table'),'Table'[Forecasted_On]=max_date))
return value_

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi @Anonymous ,
This is not the desired output. let me explain with an example:
In the below table I have three records for 5/2/2021 Forecast_DayDate. Out of three records I only want the record with the latest forecasted_On value which is 08/05/21 and Empties_Forecast is 1717.51( rest two records I want to filter).

This gives me the latest record whicg I have to display in the report.

Forecast_DayDate	Join	location	Maxdate	Brewery	Returnable_Group	Forecast_WeekDate	Empties_Forecast	Forecasted_On
5/2/2021	02/05/20211L Ab1926	1926	8-May-21	Ib	1L	8-May-21	1717.51	08/05/2021
5/2/2021	02/05/20211L Ab1926	1926	8-May-21	Ib	1L	8-May-21	1929.84	01/05/2021
5/2/2021	02/05/20211L Ab1926	1926	8-May-21	Ib	1L	8-May-21	2182.1	24/04/2021

Thanks,

Raki

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

Measure = var max_=CALCULATE(MAX('Table'[Forecasted_On]),FILTER(ALL('Table'),'Table'[Forecast_DayDate]=SELECTEDVALUE('Table'[Forecast_DayDate])))
var datee= CALCULATE(MAX('Table'[Empties_Forecast]),FILTER(ALL('Table'),'Table'[location]=SELECTEDVALUE('Table'[location])&&'Table'[Brewery]=SELECTEDVALUE('Table'[Brewery])&&'Table'[Returnable_Group]=SELECTEDVALUE('Table'[Returnable_Group])&&'Table'[Forecast_DayDate]=SELECTEDVALUE('Table'[Forecast_DayDate])&&'Table'[Forecasted_On]=max_))
return datee

Best Regards

Community Support Team _ Polly

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