cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

SUM of current and every previous month minus same month last year

I need to do this: for every current period, sum all last periods' (if they exist) plus the current's period value. Moreover, I have to exclude from the result, the value of the same month (to the current) from the last year. 

Example: 

November 2017 = (the values of all months until October 2017) + November's 2017 - November's 2016

 

Help me please !! 🙄

1 ACCEPTED SOLUTION
Super User III
Super User III

@annae As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

 

And you can add this measure once date dimension is in the model

 

Total = 
CALCULATE ( SUM ( Table[Amount] ), DATESYTD ( DateTable[Date] ) ) -
CALCULATE ( SUM ( Table[Amount] ), SAMEPERIODLASTYEAR ( DateTable[Date] ) ) 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
Super User III
Super User III

@annae As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

 

And you can add this measure once date dimension is in the model

 

Total = 
CALCULATE ( SUM ( Table[Amount] ), DATESYTD ( DateTable[Date] ) ) -
CALCULATE ( SUM ( Table[Amount] ), SAMEPERIODLASTYEAR ( DateTable[Date] ) ) 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors