Forum Discussion
Same column calculation with condition in another column
Hi,
I have a following table, and would like to calculate the cost difference from previous month per Product. For example, for Product A, the difference between May and June is 1, June and July is 4. The calculation is just a simple subtraction but I have no idea how to achieve this with DAX. It would be great if you provide a sample code. Thanks!
| Year | Month | Product | Cost | Cost difference from last month (Required result) |
| 2022 | 5 | A | 10 | 0 |
| 2022 | 5 | B | 20 | 0 |
| 2022 | 5 | C | 30 | 0 |
| 2022 | 6 | A | 11 | 1 |
| 2022 | 6 | B | 21 | 1 |
| 2022 | 6 | C | 31 | 1 |
| 2022 | 7 | A | 15 | 4 |
| 2022 | 7 | B | 25 | 4 |
| 2022 | 7 | C | 35 | 4 |
Anonymous , Create a date column
date = eomonth(date([year], [month], 1),0)
then create a new column
last month Cost =
var _last = eomonth([Date],-1)
return
[cost] - maxx(filter(Table, [Product] = earlier([Product]) && eomonth([Date],0) = _last) , [Cost])
3 Replies
- amitchandak
Super User
Anonymous , Create a date column
date = eomonth(date([year], [month], 1),0)
then create a new column
last month Cost =
var _last = eomonth([Date],-1)
return
[cost] - maxx(filter(Table, [Product] = earlier([Product]) && eomonth([Date],0) = _last) , [Cost])
Hi amitchandak
I have another question relating the original one. I would like to know how to calculate the quartely difference per Product like below. The 1st quarter is from April to Jun, and the 2nd quarter starts from Jul. I am looking for a solution to calculate the difference of quartely aggregated cost per product. I would appreciate if you provide a sample DAX code. Thanks.
Year Month Product Cost Cost difference from last quarter (Required result) 2022 4 A 10 0 2022 4 B 20 0 2022 5 A 10 0 2022 5 B 20 0 2022 6 A 10 0 2022 6 B 20 0 2022 7 A 15 15 2022 7 B 25 -10 20222 8 A 15 15 2022 8 B 25 -10 2022 9 A 15 15
amitchandak Thanks for your quick response. It worked!