cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
annae
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
parry2k
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
parry2k
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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.