Measure with logic to "look forward" for null records

[Anonymous]

Hello,

I have this scenario:

I currently have a measure that I know is incorrect but I cannot figure out how to write it to bring the desired result.

Current Measure:

Running Total Highest Primkey MEASURE =

CALCULATE(

    SUM('Table'[Running Total Highest Primkey Calc Column]),

    FILTER(

        ALLSELECTED(DimDate[Date]),

        ISONORAFTER(DimDate[Date], MAX(DimDate[Date]), DESC)

    )

)

The logic I believe I want is, for each Material: 

-If next period does not have any records for that Material, carry forward previous period's ending total  

-Else If the next period does have records for that Material, then take the value from the next period with the highest primary key for that Material

So in the example above, Oct in 2022 should be sum of Product A's value [11,502.54] + Product B's value from September [1080.27] = 12,582.81. This value should be carried forward "forever" since there are no more records for either Material.

I hope this made sense - I feel like the solution is out there and I have been looking at this for too long!

TIA

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Rank_Measure =
var _rank=
RANKX(FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])),CALCULATE(SUM([Del/finish])),,ASC)
var _if=
IF(
    MAX('Table'[Prikey])=MAXX(FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])),[Prikey])&&_rank=
    MAXX(FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])),_rank),1,0)
return
_if

Running Total Highest Primkey MEASURE =
SUMX(FILTER(ALL('Table'),
[Rank_Measure]=1),[Running Total Highest Primkey Calc Column])

2. Result:

If you need pbix, please click here.

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