Forum Discussion

mespoKTM's avatar
mespoKTM
Regular Visitor
5 years ago

Calculate sum with rolling data filter

I have a measure that calculates the end of the month based on the month selected in a histogram chart. I need to calculate a sum for documents that have as date the period between the beginning of t...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
5 years ago

mespoKTM , Not very clear. But if you have date, You can use time intelligence and date table

 

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

mespoKTM's avatar
mespoKTM
Regular Visitor
5 years ago

I'm sorry but your solution doesn't works.

I simply want to calculate the sum of revenue from the start of the year to the end of selected month.

 

Thank you so much!

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi mespoKTM ，

     

    I suggest you use lastnonblank instead of selectedvalue in your formula:

     

    99_EOMONTH_SELVALUE = EOMONTH(DATE(YEAR(NOW()),LEFT(LASTNONBLANK(Mesi[Mesi],1),2),01),0)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai