0

I'm working on some DAX query relying on a date difference. My FILTER seems to be working in most cases except when the month of @StartDate is higher than the month of @EndDate.

Here is the query :

, FILTER
(
  VALUES('Date'[Report Date]),
  'Date'[Report Date] >= FORMAT(DATE("2021","09", "01"), "dd.mm.yyyy")      //Start Date
  && 'Date'[Report Date] <= FORMAT(DATE("2022", "08", "01"), "dd.mm.yyyy")  //End   Date
)

I am not sure why this is designed to work like that. I would expect it to take every date between 09-2021 & 08-2022.

On the other hand, if I replace @Startdate with a month that is lower than @EndDate's month, it works as expected.

Should I not use >= / <= for this kind of operation ?

Thank you for your help

Limon
  • 9
  • 6
  • 2
    Why do you calculate with text not date? – Peter Aug 22 '22 at 09:03
  • What is the reason for a Format()? – Mik Aug 22 '22 at 09:16
  • I don't know much about DAX so : I don't know, I thought using Date() would use a date & not text ? As for the format, this is because the user may type (SSRS parameter) the Start/End Date themselves in this specific format. – Limon Aug 22 '22 at 11:23
  • 1
    The construction itself looks Ok and should work properly. Date() returns Date data type. It seems that the problem caused by data type. So first check what kind of data you have in your table. If you want me to see your reply print my name like @Mik. – Mik Aug 22 '22 at 12:54
  • 1
    Haaaa ! You were right @Mik, [Report Date] was a String type. I used something else for the date comparison & removed the FORMAT(). Thanks a lot for the hint ! – Limon Aug 22 '22 at 13:32

0 Answers0