Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Carry forward a value from the freeze date till month end

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!

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Frequent Visitor

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

Community Support

Can you share your sample data or pbix files?

Best regards,
Community Support Team_ Scott Chang

Frequent Visitor

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!

Community Support

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.

Frequent Visitor

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!!

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors