cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Cumulative sum up to last year yesterday.

Dear Tutors,

I am trying to sum all values up to last year yesterday ( 1/1/2016 ~ 4/2/2016) (when today is 4/3/2017)

For example

I hope PY Calc stops at 21,455, which is April 2nd value. So, I want 21,455 at April 5th as well.

My equation is

CY Calc = CALCULATE(SUM(Table[Sales], DATESYTD('Date'[Date]))/1000

PY Calc = CALCULATE([CY calc], SAMEPERIODLASTYEAR('Date'[Date].[date]))

Best Regards,

Jiin Son

3 REPLIES 3
Super User

## Re: Cumulative sum up to last year yesterday.

Hi @jiinson,

Use the date inteliggence formulas:

Cy calc = TOTALYTD (SUM(Table[Sales], 'Date'[Date]))
PY calc = TOTALYTD (SUM(Table[Sales], DATESADD('Date'[Date],-1,YEAR)))

Regards,

Mfelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Cumulative sum up to last year yesterday.

@MFelix It gives me the same results with the example picture.

Thanks,

Jiin Son

Community Support Team

## Re: Cumulative sum up to last year yesterday.

Hi @jiinson,

>>I hope PY Calc stops at 21,455, which is April 2nd value. So, I want 21,455 at April 5th as well.

You can add filter to the calculate formula to limit the calculation range or directly add a date filter at visual level filter.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |