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 DATE REGION MONTH VALUE EXPECTED VALUE 1/1/202...
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

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