Forum Discussion
COMPARE Value differences between 2 columns by DATE
You can start by creating measures to calculate the amounts in the periods you want to compare. The years in this example could be fetched from selections in slicers, current year or something else in the below formulas.
QuantityCurrentYear =
VAR CurrentYear = 2019
RETURN
CALCULATE (
SUM ( Orders[Quantity] );
KEEPFILTERS ( Orders[Year] = CurrentYear )
)QuantityPreviousYear =
VAR PrevYear = 2018
RETURN
CALCULATE (
SUM ( Orders[Quantity] );
KEEPFILTERS ( Orders[Year] = PrevYear )
)
Then create the measure that groups on vendor and part and calculates the difference:
QuantityDifference =
SUMX (
SUMMARIZE ( Orders; 'Orders'[Vendor]; Orders[Part] );
[QuantityCurrentYear] - [QuantityPreviousYear]
)
(if you use english settings, replace ; with ,)
Best Regards // Ulf
- Anonymous6 years agoNot applicable
Honnestly this is great ! thank you for this first step.
But the only problem is that i have is the following one :
Vendor Part Order Quantity Order Difference Year Jack aero 112000 5 -5 2018 Jack aero 112000 7 7 2019 BUSAIR aviation 150000 10 -10 2018 BUSAIR aviation 150000 20 20 2019 total 42 12 2019 But what i would like to see is where i have the bigger difference by part number. Or, in this case i just see for the total or if i'm selectionning one part with a slicer.
On this point, i would like a coulum, like "2019 difference", where you could see for each part easily the difference before the previous year (2018).One row out 2 will show a data... something like that
Part 2019 difference 112000 2 15000 10 total 12
thanks again for your help !