Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Carry over last values for future dates

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

DATEREGIONMONTHVALUEEXPECTED VALUE
1/1/2024APAST11
1/1/2024BPAST22
2/1/2024APAST33
2/1/2024BPAST44
3/1/2024ACURRENT/FUTURE 3
3/1/2024BCURRENT/FUTURE 4
4/1/2024ACURRENT/FUTURE 3
4/1/2024BCURRENT/FUTURE 4
5/1/2024ACURRENT/FUTURE 3
5/1/2024BCURRENT/FUTURE 4
DAX
Help
lastvalue
powerbi
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Anonymous ,

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Anonymous ,

     

    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's avatar
    lbendlin
    Super User
    2 years ago

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