Forum Discussion

AlexGallet01's avatar
AlexGallet01
Helper IV
8 years ago
Solved

SUM with datefilter

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

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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. :smileyhappy:

     

    Regards