latest value on the future

[Marwen89]

Hello,

I want to copy latest value of a mesure on the future even it s equal to 0 or null.

Exemple: copy 38068 to last date on the calendar.

 

Could you assist please with DAX formulas?

Thank you.

Regards,

[Marwen89]

Any suggestions? 

[V-lianl-msft]

Hi @Marwen89 ,

Try to create a measure like below:

Measure = 
VAR LastDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        ALL ( 'Table' )
    )
var max_ = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]=LastDate_))
VAR result =
    CALCULATE (
        SUM('Table'[Value]),
        DATEADD('Calendar'[Date],1,DAY)
    )

RETURN
    if(max('Calendar'[Date])>=LastDate_,max_,Result)

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

[Marwen89]

Hello @V-lianl-msft ,

it does not work for future  date,

here i have last value at 04/04/2022, i want it copied for the future date.

Thanks,

[BeaBF]

@Marwen89 Hi!

Can you paste some sample data and explicit expcted result?

It isn't clear.

Thx,

BF.

[Marwen89]

@BeaBF  I really want to copy the latest value of "In Transit Retail TRV Picture end period (qty)" even is null for th future, Seen that  "In Transit Retail TRV Picture end period (qty)" is a measure.

A DAX formulas is needed .

Thanks,
Regards,

[BeaBF]

@Marwen89 you can do it in Power Query, thorught Table.FillDown function, like this:

#"NEW STEP = Table.FillDown(#"PREVIOUS STEP",{"In Transit Retail TRV Picture end period (qty)"})
in
#"NEW STEP"

BF

[Marwen89]

I want a dax formula @BeaBF 

[BeaBF]

@Marwen89  if you have a table like this:

you can create a calculated column to do so:

Colonna =

VAR LastNonBlankDate =

CALCULATE (

LASTNONBLANK ('Tabella (2)'[DATE], 1 ),

FILTER (

ALL ('Tabella (2)' ),

'Tabella (2)'[DATE] <= EARLIER ( 'Tabella (2)'[DATE])

&& NOT ( ISBLANK ( 'Tabella (2)'[RETAIL] ) )

)

)

RETURN

CALCULATE (

SUM ( 'Tabella (2)'[RETAIL] ),

FILTER ( ALL ( 'Tabella (2)'),'Tabella (2)'[DATE]= LastNonBlankDate )

)

BF.

[Marwen89]

 "In Transit Retail TRV Picture end period (qty)" is a measure,

[BeaBF]

@Marwen89 Can you paste me the code of the measure?

Thx

[Marwen89]

CALCULATE (SUM ( 'In Transit Retail, Supply'[TransitPictures Quantity] ), DATEADD ( LASTDATE ( 'Period'[Date] ), 1, DAY ))

@BeaBF 

[BeaBF]

@Marwen89 So try the calculated column above with Supply'[TransitPictures Quantity] .

BF