Forum Discussion

clock0928's avatar
clock0928
Helper I
2 years ago
Solved

Creating Excel formula in Power Bi

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • clock0928's avatar
    clock0928
    Helper I
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        clock0928
        Helper I
        2 years ago

        Anonymous love your work - thank you again for the assistance 🙂