Datesbetween

Nas211 — Mon, 05 Sep 2022 12:08:35 GMT

Hi guys, I'm néw to writing Dax queries and I'm currently stuck trying to use the "Datesbetween" feature to write queries with multiple criteria. I'm trying to have a stacked trend lines with multiple years. I want the lines to be overlapping in a combined trend form.

The query bellow is not working. I'll appreciate inputs at this point.

E.g

TrendYears = CALCULATE(SUM(merged_data[Workinghours]), Datesbetween (merged_data[Date], date(2019,1,1), date(2019,12,31)),

Datesbetween (merged_data[Date], date(2020,1,1), date(2020,12,31)),

Datesbetween (merged_data[Date], date(2021,1,1), date(2021,12,31)),

Datesbetween (merged_data[Date], date(2022,1,1), date(2022, 5, 25)))

Re: Datesbetween

amitchandak — Mon, 05 Sep 2022 14:32:38 GMT

Nas211 , what are trying to achieve here, Datesbetween should use date table for continuous dates (Date

You need one measure

TrendYears = CALCULATE(SUM(merged_data[Workinghours]), Datesbetween ('Date'[Date], date(2019,1,1), date(2022, 5, 25)))

More examples

YTD =
var _omax = format(if(isfiltered('Date'),MAXX( allselected( 'Date') , 'Date'[Date]) , today()), "MMDD")
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = if(month(_max) <4, Date(year(_max)-1,4,1) ,Date(year(_max),4,1) )
return
CALCULATE(calculate([net], filter('Date'), format('Date'[Date], "MMDDD") <=_omax) ,DATESBETWEEN('Date'[Date],_min,_max))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

YTD =
var _max1 = MAXX( allselected('Date'), 'Date'[Date]) )
var _min1 = MINX( allselected('Date'), 'Date'[Date]) )
var _max = date(Year(_max1)+1, Month(_max1), day(_max1))
var _min = date(Year(_min1)+1, Month(_min1), day(_min1))
return
CALCULATE(count(salesforce_opportunity[Account Id]) ,DATESBETWEEN('Date'[Date],_min,_max))

YTD till Last month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

topic Datesbetween in DAX Commands and Tips

Datesbetween

Re: Datesbetween