Forum Discussion

blazko's avatar
blazko
Helper III
9 years ago
Solved

Last Year calculation, calculated column

Hi, so I have this calculated column with monthly sum of orders qty :

monthly_sum = CALCULATE(
	SUM('BASE data'[qty]);
	ALLEXCEPT('BASE data';
		'BASE data'[YEAR];
        'BASE data'[MONTH]))

My question is, how do I calculate the monthly sum, same month, but last year. So I would have two columns:
- monthly_sum - this year

- monthly_sum LY - from the last year.

 

I dont want to calculate new table.

 

Thanks in advance for any reply!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi blazko,

     

    The formula below should work in your scenario. :smileyhappy:

    monthly_sum LY =
CALCULATE (
    SUM ( 'BASE data'[qty] );
    FILTER (
        ALL ( 'BASE data' );
        'BASE data'[YEAR]
            = EARLIER ( 'BASE data'[YEAR] ) - 1
            && 'BASE data'[MONTH] = EARLIER ( 'BASE data'[MONTH] )
    )
)

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi blazko,

     

    The formula below should work in your scenario. :smileyhappy:

    monthly_sum LY =
CALCULATE (
    SUM ( 'BASE data'[qty] );
    FILTER (
        ALL ( 'BASE data' );
        'BASE data'[YEAR]
            = EARLIER ( 'BASE data'[YEAR] ) - 1
            && 'BASE data'[MONTH] = EARLIER ( 'BASE data'[MONTH] )
    )
)

     

    Regards