Carry forward a value from the freeze date till month end

[sandyn-2303]

I am facing getting the result as expected. My table has Daily Actuals and Month Forecast value. I want the forecast value to freeze on 10th Business day of the month (marked in Red, 2/14/2024) and carry forward the value till end of the month.  I created a column to get the freeze date (10th business day) then I created another column to get the Forecast till the freeze date but not further (Forecast till freeze, marked in blue). I want the new column/measure - New Forecast (marked in green) where the forecast value is repeated after 2/14/2024 till mont end as shown below. Any help is much appreciated!

Thanks!

[v-tianyich-msft]

Hi @sandyn-2303 ,

I made simple samples and you can check the results below:

You can change Freeze Date to your measure and it should work.

Measure = var _Date = MAX('Table'[Date])
var _Freeze = MAX('Table'[Freeze Date])
var _t = ADDCOLUMNS('Table',"a",IF(_Date<_Freeze,MAX('Table'[Month Forecast]),MAXX(FILTER(ALL('Table'),[Date]=EARLIER([Freeze Date])),[Month Forecast])))
RETURN MAXX(_t,[a])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[sandyn-2303]

Thanks for your response. I tried the DAX you provided and here is what I see. Sorry, I should have mentioned I have some filters on the table to get only Current month forecast data and the dax is ignoring coz of ALL applied in the filter. Trying to figure out how it ended up showing 2613 only across the freeze date.

Thanks

[v-tianyich-msft]

Hi @sandyn-2303 ,

Can you share your sample data or pbix files?

Best regards,
Community Support Team_ Scott Chang

[sandyn-2303]

I tried to attach the file I prepared but looks like I dont not have access to attached or share files. Do you know of any other option?

Attached some snapshots of data, datamodel and dax used if it helps.

Freeze Date = IF(MONTH(TODAY()) = MONTH(LASTDATE(Forecast_Data[REPORT_DATE])), CALCULATE(MAX(Forecast_Data[REPORT_DATE]), Accounting_Date[ACCOUNTING_DAY] = 10))

Measure =

var _Date = MAX(Forecast_Data[REPORT_DATE])

var _Freeze = MAX(Forecast_Data[Freeze Date])

var _t = ADDCOLUMNS('Forecast_Data',"a", IF(_Date < _Freeze, MAX(Forecast_Data[Month Forecast]), MAXX(FILTER(ALL(Forecast_Data), Forecast_Data[REPORT_DATE] = EARLIER(Forecast_Data[Freeze Date])),Forecast_Data[Month Forecast])))

RETURN MAXX(_t,[a])

Thanks!

[v-tianyich-msft · Accepted solution]

Hi @sandyn-2303 ,

I think the key to the problem is Freeze Date, how to have Freeze Date for each row of data.Like the sample I made, since I don't know your exact data structure, I don't know how you defined it. Try writing Freeze Date for each row of data, for example February is all about February 14th.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[sandyn-2303]

Thank you for your continuous support. Your suggestion actually worked. I changed the measure to show freeze date for all rows and tweaked your measure slightly. It worked!!