SUM with datefilter

[AlexGallet01]

Hello,

I want to do a sum of value where my date start 30/06/(YEARS(TODAY())-3 AND end date 30/06/YEAR(TODAY()) but when i try this i have an error message.

Can you help me please ?

Reguards

[v-ljerr-msft]

Hi @AlexGallet01,

Have you tried the solution provided by @TomMartens above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Abduvali]

@AlexGallet01

This should work just adjust to your table and column names:

Measure = calculate(COUNT(PBIDataSource[No.]), filter(ALLSELECTED(PBIDataSource),

  PBIDataSource[Date] > date(2017,01,01) &&
  PBIDataSource[Date] < date(2017,02,01)

 )

)

Regards

Abduvali

[TomMartens · Accepted solution]

Hey,

you can uses this DAX statement to create your measure

Measure = 
var yearToday = YEAR(TODAY())
var yearStart = yearToday - 3
var theDatesTable = DATESBETWEEN('Calendar'[Date], DATE(yearStart, 6, 30),DATE(yearToday, 6, 30))
return
CALCULATE(
	SUM('FactWithDates'[Amount])
	,theDatesTable
)

Please be aware that this Measure heavily relies on the common practice to use a separate calendar/date table, where this date table relates to your fact table, please also have a look to at this site where a lot of common date related calculations are explained: daxpatterns.com/time-patterns

Hm, I'm wondering if your start should be 7/1/year(today())-3 (representing the 1st of July 3 years ago), if you want to cover complete 12 months.

Regards

Tom