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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

naidu_sandhya_0-1709075528881.png

 

Thanks!

1 ACCEPTED SOLUTION

Hi @naidu_sandhya ,

 

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.

View solution in original post

6 REPLIES 6
v-tianyich-msft
Community Support
Community Support

Hi @naidu_sandhya ,

 

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

vtianyichmsft_0-1709087157050.png

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.

 

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.

naidu_sandhya_0-1709095362817.png

Thanks

Hi @naidu_sandhya ,

 

Can you share your sample data or pbix files?

 

Best regards,
Community Support Team_ Scott Chang

 

 

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.

naidu_sandhya_0-1709101918702.pngnaidu_sandhya_1-1709102013517.png

naidu_sandhya_3-1709102239015.png

 

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!

Hi @naidu_sandhya ,

 

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.