Calculating getting monthly production into daily sales without flattening the tables.

[alfranco17]

I have a report where I need to calculate daily sales * production / monthly production. I get it by flattening three tables this model (here's the file, in case you would like to take a look):

... into this model, using Power Query:

It works a lot faster than the original SUMIFS and VLOOKUPS, and I was pretty happy with myself. Until I read Rob Collie's DAX FORMULAS FOR POWER PIVOT:

"Some Excel Pros who know their way around a database also write queries that flatten the data into one table before it's ever imported.

You do not need to do either of these anymore. In fact, you should not".

I have been trying to solve this without flattening for over four hours now. The closest I think I got was this formula to pull daily production into sales:

=CALCULATE(SUM(Production[Production]),Production[Date]=Expense[Date])

And then I tried this to get the whole month:

=CALCULATE(SUM(Production[Production]),Production[FY Month]=Expense[FY Month]).

But it did not work and besides it feels like flattening the table again.

I tried to use DATESBETWEEN, FILTER with ENDOFMONTH, DATE.

The result I'm trying to get is this one (it comes from the flattened version).

I would really appreciate any hints. I tried to do it with measures in the pivot table, but if I removed either the account, cost center or day it looked like it joining all against all, and the total would go from $25 to 9 times 25, one for each of the lines.

Thanks.
Armando.

 

[alfranco17]

After a lot of trying, I found a way. I'll post it in case someone needs it too:

This formula will calculate the monthly budget:
=calculate(sum(budget[Budget]),datesbetween(calendar[date],startofmonth([calendardate]),endofmonth(calendar[date])))

It took forever to get it to work. You need to make sure to use only dates in the calendar table, and I still can't figure out what was wrong with the formula for three hours...