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.
