Carry over last values for future dates

[bd27]

Hello, I have a data set that looks like below. Looking to carry over the last values from each region for all the Current/Future date values

DATE	REGION	MONTH	VALUE	EXPECTED VALUE
1/1/2024	A	PAST	1	1
1/1/2024	B	PAST	2	2
2/1/2024	A	PAST	3	3
2/1/2024	B	PAST	4	4
3/1/2024	A	CURRENT/FUTURE		3
3/1/2024	B	CURRENT/FUTURE		4
4/1/2024	A	CURRENT/FUTURE		3
4/1/2024	B	CURRENT/FUTURE		4
5/1/2024	A	CURRENT/FUTURE		3
5/1/2024	B	CURRENT/FUTURE		4

[v-yangliu-msft · Accepted solution]

Hi  @bd27 ,

Here are the steps you can follow：

1. Create calculated column.

EXPECTED VALUE =
var _today=TODAY()
var _mindate=DATE(YEAR(_today),MONTH(_today),1)
var _max=
    MAXX(
        FILTER(ALL('Table'),'Table'[DATE]<_mindate&&'Table'[REGION]=EARLIER('Table'[REGION])),[DATE])
return
IF(
    'Table'[DATE]>=_mindate,
    MAXX(
        FILTER(ALL('Table'),'Table'[DATE]=_max&&'Table'[REGION]=EARLIER('Table'[REGION])),[VALUE]),[VALUE])

2. Result:

Best Regards,

Liu Yang

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

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?