Difference of summed values in two different tables with date grouping

[kjohn10]

I've looked though numerous other questions, similar to this problem, but I cannot get any possible posted solution to work. I have 2 (fact) tables, 'Production' and 'Orders', with a (dim) table that links them by an ID. See image. This relation is open to change based on feedback here!

The dates within Production and Orders do not align 1-1, Orders come in on any given day, and Production is run another day. Also Orders are projected 18 months into the future, so there are numerous entries with dates that will have no Production numbers. What I am trying to do is group these by months, but I dont explicitly want to do that, as the metric may change to weekly, so I'd like to get a sum and difference of these without grouping by dates explicitly...if possible, e.g. let PowerBI do the date aggregation in the visual (the lower table result shown in the image). I would also like the visual to slice (filter) by ItemID, or by product family, or product form.

I'd like to sum the Production numbers, for each Item, in each month (again this could change to week), and then subtract the sum of that product's orders in that same month (or week) for that item. This difference gives me an "adherence" metric.

Do I need to create calculated columns that separate the Dates into Year, Month, and link (relate) them this way? I just cannot figure out what 'Measures' I might need to create to then get a calculation that is the difference between production and orders for each item in any particular date range (month, week).

Suggestions on where to start? 

[Anonymous · Accepted solution]

I'm having diffculting in working out exactly what the problem you are facing is, but my expectation is that you should relate both your Production and Orders via your Items table.  You should be able to do nearly everything you have described by using Measures.

You should create a Production Sum measure and a Orders Sum measure.  Since these are context sensitive, they will recalculate based on how you use those within other measures, or on visualisations.

Depending on how this expands, having a Dim Date table that has the Year/Month precalculated may be useful.  You might be able to get away with simply having a YYYYYMM column to help you group dates into a single month.

[kjohn10]

@Anonymous thats correct, I have a relation between Production and Orders through the Item table. I also created the sum measures for both orders and productions, as well as creating a Date Dim table, however I havent been able to get it to just contain YYYY-MM column so that I can have it relate to Prod and Ord date values. The Date table has a range dynamically built on MIN(Production) and MAX(Orders), and it populates a row for every day. Therefore I cant relate to just YYYY-MM because neither tables have a unique value! Is there a way to build a CALANDER(..) Date table with just YYYY-MM entries?

When I try to build the difference column chart (with months as axis), one of the values (either Production or Orders) sum will show up correctly, but as when I add the other value, it looks like its just summing everything over all times, and gives the same total for every time period. When it calcs the difference, its always taking that one sum and subtracting it over each time period, instead of taking the actual sum of that period. This is likely cause I havent gotten the Date Dim setup correctly yet! The image shows in a table (instead of column chart) what it's calculating for sums.

Just to note though, I dont want to have to force a relation via Date Dim...I'd like the visualization do that for me. So I need the relation to work through either the Item table, or I need another way to calc the difference via 'RELATED' measures...?

[Ashish_Mathur]

Hi @kjohn10,

Share the link from where i can download your file.

[kjohn10]

I've cleaned up the project and simplified the data model, and using the Date Dim table, I was successful at using the SUM(Order Qty) and SUM(Prod Qty) measures to get a correct difference between them. Thank you both for your assistance in leading this in the right direction. I'll continue to work on it and see if I can remove the Date requirement, although I suspect that wont be possible.

[Zubair_Muhammad]

Hi @kjohn10

See the attached file here. Hope it helps

I believe adding YEAR and MONTH calculated columns in both tables would make subsequent calculations very easy.

Then you can pull information from Orders Table into Production Table or vice versa using combination of CALCULATE and FILTER

Order_Qty =
CALCULATE (
    SUM ( Orders[OrderQuantity] ),
    FILTER (
        Orders,
        Orders[ItemID] = SELECTEDVALUE ( Production[ItemId] )
            && Orders[YEAR] = SELECTEDVALUE ( Production[YEAR] )
            && Orders[MONTH] = SELECTEDVALUE ( Production[MONTH] )
    )
)

MEASURE FOR difference is

Diff =
SUM ( Production[ProdQuantity] ) - [Order_Qty]

[kjohn10]

@Zubair_Muhammad Thanks, I'll give your example a try and see what I get. I've been busy with another data set so I haven't had a chance to try these yet. However after a quick look, you are using filters with related [YEAR] and [MONTH] between them, and again, I don't want to have to rely on date dims to do this, the visual should do this aggregation!...if possible.