Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jiinson
Helper I
Helper I

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

4-3-2017 4-45-48 PM.png

 

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

 

Thanks in advance

 

Best Regards,

Jiin Son

 

 

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
MFelix
Super User
Super User

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

Thanks,

Jiin Son

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.