DAX expression to calculate sums per date

[Anonymous]

Hi all,

I am trying to create a table within a report that shows the actual hours and scheduled hours for a list of machines. The actual hours and list of machines are in one table and I have connected another sheet that contains values for the scheduled hours (shown below). The two sheets have a relationship by the machine name. In my final table, I want to show Plant 1(machines 101-130), Plant 2(201-...) the sum of run hours per day and scheduled hours per day, but right now it is summing all the scheduled hours together. Is there a DAX measure or another method I can use to filter the scheduled hours so it will sum the hours by the machines in the same plant for the same day. Below is also a pic of the current table in the report where the value for plant 1 on 4/1/2019 should be 387.

Thanks!

Scheduled hoursRun hours (also has date column)Current table

[HotChilli · Accepted solution]

You will need to re-work the model so that actual and scheduled can be connected on date and machine ID.

One simple way of doing this would be to merge the two queries in Power Query (on Date and machine).

This will give you one table with all the right data.

If you want to slice and dice by date or do more complex date-related operations, create a date table in the model and add a relationship to the new single table.

[Anonymous]

Thanks that seeemd to work, only problem is now if someting ran more than one job in a day it repeats the scheduled hours. Could it be grouped by machine and date, then sum the run hours and show one value for scheduled?

[HotChilli]

Sounds good.  Do that before the merge.

Use 'Group By' on the Home ribbon. Choose 'Advanced' so you can group by machine AND date.

[Anonymous]

That did the trick, thanks!