Average for last 30 days if avalaible, otherwise last known value

[pawelj795]

Hi,

I'm currently calculating the materials average unit price in the last 30 days.
In some cases, formula return blank, because there weren't any purchases for particular items.

For those cases, I want to return the last available value - I mean the last available purchase.

Any idea how to do this?

Thanks in advance 😉

[pawelj795 · Accepted solution]

@amitchandak 

I did some adjustments and now it looks like this.


It seems to be working, but maybe there is something that I've missed.

Please verify, thanks

[v-lionel-msft]

Hi @pawelj795 ,

You can refer to the formula.

Measure = 
VAR x = 
CALCULATE(
    AVERAGEX(
        'Table',
        DIVIDE( 'Table'[c1], 'Table'[c2] * 'Table'[c3], BLANK() )
    ),
    DATESINPERIOD( 'Table'[Date], MAX('Table'[Date]), -30, DAY ),
    USERELATIONSHIP( )
)
RETURN
IF( 
    x = BLANK(),
    CALCULATE(),
    x
)

Best regards,
Lionel Chen

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

[amitchandak]

@pawelj795 , This can you data of last available date with help from a date table. so when 30-day value is null you can use this

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

[pawelj795]

Hi @amitchandak 

Thanks for your reply, but could you help me with the implementation of your adjustment?
I tried by myself, but it returns only blanks.

[pawelj795 · Accepted solution]

@amitchandak 

I did some adjustments and now it looks like this.


It seems to be working, but maybe there is something that I've missed.

Please verify, thanks

[amitchandak · Accepted solution]

@pawelj795 , seem fine. you could have created a couple of measures and just handled then here.