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 the year and the calculated date.

 

This is the Sum measure:

99_FatturatoYTD = CALCULATE(SUM(Fatturato_No_ArticoliNulli[DO30_IMPORTO]),
FILTER(ALL(Fatturato_No_ArticoliNulli),
AND(Fatturato_No_ArticoliNulli[DO11_DATADOC]>=DATE(YEAR(NOW()),01,01),
Fatturato_No_ArticoliNulli[DO11_DATADOC]<=[99_EOMONTH_SELVALUE])))

This is the Data measure:
99_EOMONTH_SELVALUE = EOMONTH(DATE(YEAR(NOW()),LEFT(SELECTEDVALUE(Mesi[Mesi]),2),01),0)
 
If substitute the measure with the exact date it works
Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
        Icon for Community Support rankCommunity 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

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

    Hi mespoKTM ,

     

    If you don't select any bar or select mulitiple bars in histogram chart,  SELECTEDVALUE(Mesi[Mesi]) will not get blank value in your formula, you should use SELECTEDVALUE(Mesi[Mesi],"12") in your formula  to show value  when the context for columnName has been filtered down to zero or more than one distinct value. 

     

    For more details, please refer to https://docs.microsoft.com/en-us/dax/selectedvalue-function

     

    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