Getting previous summed year value

[awff]

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

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft]

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.

[amitchandak]

@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 ])

[awff]

Thanks @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])  ))