Forum Discussion
COMPARE Value differences between 2 columns by DATE
Hi the community,
I'm making progress in DAX but i'm still stucked on begginer issues.
Imagine i have this kind of data :
| Vendor | Part | Condition | Quantity | Net Order | Year |
| Jack aero | 1123040 | Scrap | 5 | 1000 | 2018 |
| Jack aero | 1123040 | Scrap | 8 | 2000 | 2019 |
| Jack aero | 1132050 | Overhaul | 2 | 1500 | 2018 |
| Jack aero | 1132050 | Overhaul | 6 | 3000 | 2019 |
I would like to know how to calculate the difference in term of quantity and net order between 2018-2019 by part and by Vendor.
By example a column or a formula which gave me like for the part 1123040 -3 and 1000$
Thank you so much in advance for any help !!
Best
2 Replies
- AnonymousNot applicable
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
- AnonymousNot 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 !