Creating Excel formula in Power Bi

Hi clock0928 ,

I will start by showing you my test data:

To make it easier to see, I created a week table and used that to create a slicer:

I then used DAX to create the following three measures:

Total_CarsOnHand = 
CALCULATE(
    SUM('Work On Hand'[CarsOnHand]),
    FILTER(
        ALLSELECTED('Calendar'[WeekOfYear]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Slicer'[WeekNo])
    )
)

LastWeek_Total_CarsOnHand = 
CALCULATE(
    SUM('Work On Hand'[CarsOnHand]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Slicer'[WeekNo]) - 1, ALL('Calendar')
    )

LastWeek_Total_CarsCounted = 
CALCULATE(
    SUM('Daily Statistics'[CarsCounted]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Slicer'[WeekNo]) - 1, ALL('Calendar')
    )

The three measures can then be used to perform the calculation steps you require:

Measure = 'Work On Hand'[Total_CarsOnHand] - 'Work On Hand'[LastWeek_Total_CarsOnHand] + 'Daily Statistics'[LastWeek_Total_CarsCounted]

The final result is shown below:

Best Regards,

Dino Tao

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