Creating Excel formula in Power Bi

[clock0928]

Hi all,
I understand that this is probably a specific issue to myself, however if you have time to assist that would be greatly appreciated!
Currently I have a 4 tables in my Power Bi:

1. Calendar
   • Date
   • WeekOfYear
2. Team Members
   • Full Name
3. Daily Statistics
   • Date (linked to Calendar table)
   • Full Name (linked to Team Members table)
   • CarsCounted
   • ToolsCounted
4. Work On Hand
   • Date (linked to Calendar table)
   • Full Name (linked to Team Members table)
   • CarsOnHand
   • ToolsOnHand

From this, I want to replicate a formula from Excel that looks something like this - 
"Current Weeks CarsOnHand" - "Previous Weeks CarsOnHand" + "Previous Weeks CarsCounted" (screenshot attached)

What would be my best way to approach this in Power Bi? I feel like I have all the right information for it to be achieved, just having some troubles with executing it.
Any help is appreciated.

[clock0928]

This is fantastic, thank you @v-junyant-msft !
Is there a way to have the filter based on my calendar table if I already have a field of "WeekOfYear"?

[v-junyant-msft]

Hi @clock0928,

Of course you can, just change the 

SELECTEDVALUE('Slicer'[WeekNo])

into

SELECTEDVALUE('Calendar'[WeekOfYear])

in the three DAXs, and I've tested them and they still work fine in my sample data.

Best Regards,
Dino Tao

[clock0928]

@v-junyant-msft love your work - thank you again for the assistance 🙂

[v-junyant-msft · Accepted solution]

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.