Forum Discussion
Datesbetween
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)))
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))
1 Reply
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))