Filter a table twice and populate every date with a value that is related to the first every month

[Anonymous]

Hi.
I have a table with forecasted values for each month of the year. Every new quarter a new forecast is entered in new rows for the months that has not passed yet. An example table looks like this:

ID	Forecasted month	Month the forecast was made:	Forecasted values	Forecast date:
1	202205	202204	345317	20220501
2	202205	202204	631403	20220501
4	202205	202204	759131	20220501
1	202206	202204	324189	20220601
2	202206	202204	548996	20220601
4	202206	202204	695510	20220601
1	202207	202204	266388	20220701
2	202207	202204	459766	20220701
4	202207	202204	585520	20220701
1	202208	202204	373847	20220801
2	202208	202204	639365	20220801
4	202208	202204	832754	20220801
1	202207	202207	26638,8	20220701
2	202207	202207	45976,6	20220701
4	202207	202207	58552	20220701
1	202208	202207	37384,7	20220801
2	202208	202207	63936,5	20220801
4	202208	202207	83275,4	20220801

The column on the far right is a custom column I created that takes the data from Forecasted months, adds "01" and converts to date.
What I want is a matrix that sums the forecasted values, divides them by number of working days and then populate that value on each day in the matrix (nevermind if the days in the matrix is a working day or not)

I have that in the following dax:
"Measure =
var _Date = max('Datetable'[Date])
var Forecast = CALCULATE(sum(Valuetable[Forecasted values]),REMOVEFILTERS(Datetable),Valuetable[Forecast date:]=date(year(_Date),month(_Date),1))
var NumberOfWorkdays = calculate(MAX(Datetable[Workday no:]),all('Datetable'),'Datetable'[Year]=year(_Date),Datetable[MonthOfYear]=month(_Date))
var ForecastPerDay = DIVIDE(Forecast,NumberOfWorkdays,0)
Return ForecastPerDay"

And it works fine. The problem is that I want to filter the dax above to only use the latest forecast. My thought was to add a filter in the calculate function to use the MAX formula on the "Month the forecast was made" column. It works but in the same time it stops to populate every day in the matrix. Dax and result below:

"Measure =
var _Date = max('Datetable'[Date])
Var LatestForecast = MAX(Valuetable[Month the forecast was made:])
var Forecast = CALCULATE(sum(Valuetable[Forecasted values]),REMOVEFILTERS(Datetable),Valuetable[Forecast date:]=date(year(_Date),month(_Date),1),Valuetable[Month the forecast was made:]=LatestForecast)
var NumberOfWorkdays = calculate(MAX(Datetable[Workday no:]),all('Datetable'),'Datetable'[Year]=year(_Date),Datetable[MonthOfYear]=month(_Date))
var ForecastPerDay = DIVIDE(Forecast,NumberOfWorkdays,0)
Return ForecastPerDay"

If I change the Latestforecast from dynamic to a static value (Valuetable[Month the forecast was made:]="202207") it works but I need it to be dynamic.

Basucally my expected result is that the value 71304 populate every date in june 2022 and then from 1st july and forward it is 6246 every day until a new value 1st of August and so on.

The relationships looks like this, if that matters:

I have no idea why it doesn't work the way I expect it to and I have had no lock looking around forums and youtube. Thanks in advance for any help.

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

"The problem is that I want to filter the dax above to only use the latest forecast. "  If you just want to get the latest forecasted values, you can get it by the largest ID and the Forecasted month. 
(1) create the below measure

LastestForecast = 
var _maxmonth=CALCULATE(MAX(Valuetable[Forecasted month]),ALL(Valuetable))
var _maxId=CALCULATE(MAX(Valuetable[ID]),FILTER(ALL(Valuetable),Valuetable[Forecasted month]=_maxmonth))
return CALCULATE(MAX(Valuetable[Forecast date:]),FILTER(ALL(Valuetable),Valuetable[ID]=_maxId && Valuetable[Forecasted month]=_maxmonth))

(2) then use it in your measure

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi, thank you for responding.
I'm not sure I explained it right. 
Of the columns i want all "ID" (no filter), All "forecasted month" (no filter), All "forecasted value" (no filter), All "forecast date" (filter =date(year(max(date)),month(max(date)),1)
And on the "Month forecast was made" I want the highest/latest values.

It should work like this:

Forecasted month:	Month forecast was made:
202206	202204
202207	202204
202207	202207

I would want the first rows value since that month isnt present in the forecas made in july.
But I don't want the second row since that is replaced by the last row (latest forecast)

And at the same time the datefilter Valuetable[Forecast date:]=date(year(_Date),month(_Date),1) need to work so it populates every date in a matrix with dates as rows and not just the first every month.

I can get one of these filters to work with dax I wrote in my original post but not both. Either it takes the right forecasts (the green ones in this example) but only gives a value on the first every month OR it gives a value each date but only gives a value using the "month forecast was made"=202207 (the last two rows in the example above.

Did I clear anything up about my problem? Or maybe I made it less clear😅