Calculate absolute variance within one column

[Anonymous]

Hi everybody,

I have a problem I've been trying to solve for some weeks now, but I can't come up with a solution and on Google and this forum I didn't find anything either.

I want to calculate the absolut variance between the sales and the forecast of a month. I have the sales data on a customer order level, so in my table it's not yet summed up to the monthly total. The forecast is on the monthly level. To make it even more complicated, the sales and the forecast volumes are not in different columns, but in one (Volume in LT, BT, EUR). There is an additional column named DataType where I declare what kind of volume (Sales, Forecast, Budget etc.) it is. 

Product ID	Date ID	Customer ID	Volume in LT	Volume in BT	Volume in EUR	DataType
70556600	31.03.2020	Customer x	200	140	280	Forecast N-1
70556600	31.03.2020	Customer x	150	105	210	Forecast N-3
70556600	01.03.2020	Customer x	10	7	14	Sales
70556600	04.03.2020	Customer x	15	10,5	21	Sales
70556600	15.03.2020	Customer x	12	8,4	16,8	Sales
70556600	18.03.2020	Customer x	8	5,6	11,2	Sales
70556600	23.03.2020	Customer x	7	4,9	9,8	Sales

I want to create a measue that calculates the sum of the absolut differences between the forecast and the total sales for one month on different granularity levels. Product-level would be the abs diff for every product in one month. Product-customer level would be the abs dif of every customer-product combination for one month, etc.

How would I do that in DAX? I hope I made clear, what I'm trying to do.

Your help would be massively appreciated!

Edit: To make it (hopefully) a bit clearer: I want something like (pseudo-code): 

sum( abs( Volume in LT (where DataType = Sales) - Volume in LT (where DataType = Forecast N-1))) It should calculate the abs-expression for every month-product combination and then sum all abs-results up. 

[Greg_Deckler]

So, do you want to break the monthly forecast evenly across the month or do you want to sum up the sales for the month and compare to the forecast?

[Anonymous]

Thanks for the quick reply, Greg!

I want to sum up the sales to a monthly total and then compare to the forecast.