Calculate sum with rolling data filter

[mespoKTM]

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

[v-deddai1-msft]

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

[amitchandak]

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

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]

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