How do I SUM total production by month when i have hundreds of rows of production by daily date?

[Jacklyn]

I have monthly budget production data, actual daily production data, prod category and prod location. I'm trying to compare budget vs actual production. The budget prod figure is in months whereas Actual production data is daily. 1) How do I sum up actual production by month so I can compare to budget prod figure?  2) Also I need to compare monthly budget to monthly actual by product category and also product factory (multiple locations). I'm 1 month-old new to Power Bi, and i'm struggling to produce a report for work. All help is appreciated!

[Anonymous · Accepted solution]

Hi @Jacklyn ,

I have built a data sample:

1. Production by each month:

Monthly Production = CALCULATE(SUM('Table'[Actual production]),ALLEXCEPT('Table','Table'[Date].[Year],'Table'[Date].[Month]))

2. Production by each month , each category and each factory:

Monthly Production by category & factory = CALCULATE(SUM('Table'[Actual production]),ALLEXCEPT('Table','Table'[Date].[Year],'Table'[Date].[Month],'Table'[Product Category],'Table'[Product Factory]))

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

[Jacklyn]

Thank you heaps Eyelyn!

[ronrsnfld]

In Power Query (Home=>Transform),

• Add a column that returns the first day of each month from your date column
  • eg "=Date.StartOfMonth([Date])"
• Group By that custom column and use the aggregations to return what you need.

[Jacklyn]

Thank you too for this solution 😃