Forum Discussion
Helper IIIGetting previous summed year value
Hi All,
I'm needing some help on getting a previous year's summed total value, except I can't SAMEPREVIOUSLASTYEAR or DATEADD because there are some years there the customer has multi-year contracts. This will allow me to show the price increases/decreases for each client.
note again that this is a sum of many sub-products in the product type.
| YEAR | CLIENT | PRODUCT_GROUP | PRODUCT_TYPE | Product_Value | DESIRED |
| 2018 | Company123 | Maintenance | SAAS | 1000 |
|
| 2020 | Company123 | Maintenance | SAAS | 1200 | 1000 |
| 2021 | Company123 | Maintenance | SAAS | 1100 | 1200 |
| 2022 | Company123 | Maintenance | SAAS | 1500 | 1500 |
The below calculated column formula seems to result in a cumulative total which is incorrect... I've also tried with an index but does not seems to return the right values.
Previous_Price =
Var PrevProd = 'table'[PPRODUCT_TYPE]
Var PrevStartYear = year('table'[Start_Date__c])
Var Acct = 'table'[CLIENT]
VAR _1 =
Calculate(
SUM('table'[Product_Value]),
FILTER (
all('table'),
'table'[CLIENT] = Acct &&
'table'[PPRODUCT_TYPE] = PrevProd &&
year('table'[Start_Date__c]) < PrevStartYear
))
RETURN
_1
Hi, awff ;
Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
awff , best is to create a separate year table(say Date) and then you can get it easily
example
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
awffThanks amitchandak I do have a date table, but for my example table i had left that out.
Whilst it's simple to get previous year value, there are years there the client has bought for a two year term (e.g. 2018 to 2020), so time based dax may not work as 2020 is missing below:
Is there a way to perhaps only look back where current year > previous?
- amitchandak
awff , Then you have to add client , product type and product group too.
It is not the date, it can be yeat table too, as long as you can create it with distinct year
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Table'),'Table'[Year]=max('Table'[Year]) && 'Table'[clinet]=max('Table'[clinet]) && 'Table'[product type]=max('Table'[product type]) && 'Table'[product group]=max('Table'[product group]) ))
v-yalanwu-msftCommunity Support
Hi, awff ;
Try it.
Previous_Price = VAR _1 = CALCULATE ( SUM ( 'table'[Product_Value] ), FILTER ( ALL ( 'table' ), 'table'[CLIENT] = EARLIER ( 'table'[CLIENT] ) && 'table'[PPRODUCT_TYPE] = EARLIER ( 'table'[PPRODUCT_TYPE] ) && YEAR ( 'table'[Start_Date__c] ) < YEAR ( EARLIER ( 'table'[Start_Date__c] ) ) ) ) RETURN _1
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- v-yalanwu-msft
Hi, awff ;
Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
