Forum Discussion

Chris_23's avatar
Chris_23
Icon for Helper II rankHelper II
4 years ago
Solved

Gap filler - previous value

Hello all,

 

i've two connected table. One with the price development of some articels and one for the time period columns.

 

But there isn't always a value for any column. So the goal is, if there isn't a value for column x, check if there is one in column x-1 and take this one:

 

 

My approach:

DUC = if(CALCULATE(VALUE([DirctUnitCost]))=Blank() , CALCULATE(sum(Preisentwicklung_FLK[Direct Unit Cost]),DATEADD(Preisentwicklung_FLK[Lastmth.Date],-1,MONTH)), CALCULATE(sum(Preisentwicklung_FLK[Direct Unit Cost])))
 
But unfortunately the results didn't differ to the measure(above picture):
DUC= sum(Preisentwicklung_FLK[Direct Unit Cost])
 
Any suggestion on how to handle the problem?
 
Kind regards
Chris
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Chris_23 ,

     

    Please try the following formula:

     

    Measure = 
IF (
    [DUC] = BLANK (),
    CALCULATE ( [DUC], DATEADD ( Lastmth[Date], -1, MONTH ) ),
    [DUC]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    This is the "penultimate value" pattern.

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Chris_23 ,

     

    Please try the following formula:

     

    Measure = 
IF (
    [DUC] = BLANK (),
    CALCULATE ( [DUC], DATEADD ( Lastmth[Date], -1, MONTH ) ),
    [DUC]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.