Power BI use a previous date measure to in future date measure calculation

[alotcat]

Hi,

I am trying to replicate this excel version of report into Power BI. However, I am struggling to create a measure that will need to use previous day measure. I attched the demo PBIX file as well. I was hoping to create a var table, Past date using one measure, future date using a different measure and return value based on an if statement. But doesn't look like I can use date in the if statement. 

Excel: Expected Result Excel 

PBIX: pbix demo 

• Actual Open Inv - known value
• Target Open Inv - same as previous day Projected Close Inv
• Target # Delivery and Target # Sales: both are manual input
• Projected Close Inv: see calculation in the screenshot below

Thanks,

Sophie

[v-yalanwu-msft]

Hi, @alotcat ;

Please try it.

Forcast Close Inv TEST = 
VAR PAST =
    CALCULATE(SUM(Sheet1[Actual Open Inv])+SUM(Sheet1[Target # Delivery])*38 - SUM(Sheet1[Target # Sales])*110)
VAR FUTURE = 
    CALCULATE([Forcast Close Inv]+SUM(Sheet1[Target # Delivery])*38 - SUM(Sheet1[Target # Sales])*110)
RETURN
    IF(MAX([Date])<TODAY(),PAST,FUTURE)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alotcat]

Hi,

Thank you for the response. For Forecast Close Inv in the future, the calculation will need to use previous day forecast inv instead. Basically it will need to use the measure result as an input. The expected result is in the attached excel sheet. I have a screenshot below as well.

For example, when I posted the questions it was 4/12, so 4/13 Forecast Close Inv is 4/12 Forecast Clost Inv + Target Delivery * 38 - Target # Sales * 110