Forum Discussion
Solved
Generateseries with Dates
Hi, so i'm trying to create column with flag, 1/0 to filter on dates.
The dates i'm looking for are the last 3 quarters to date.
So today being 2024-01-11 it should return 1 for dates between 2023-04-01 and 2024-01-11.
I've created this, the start date is fine but the end date give dates past 2024-01-11
Rolling 3 Quarters =
var _currentDate = TODAY()
RETURN
if(DATEDIFF(_currentDate, Date_Dim[CAL_DATE_YR_MTH_DAY_E], QUARTER) in
GENERATESERIES(
-3,
var _todayDate = TODAY()
RETURN
_todayDate,
1)
,1,0)
Help!
thanks in advance.
That worked! I only had to add Datevalue() to my date dim column, see below. Thanks so much!
Column Flag For Rolling prev 3 Quarters and current QTD =var q = QUARTER(EDATE(TODAY(),-9))var y = YEAR(EDATE(TODAY(),-9))return if(DATEVALUE([CAL_DATE_YR_MTH_DAY_E]) >= DATE(y,q*3-2,1) && DATEVALUE([CAL_DATE_YR_MTH_DAY_E])<= TODAY(),1,0)
20 Replies
That is four quarters.
thats fine, how about the issue with the end date
If you want rolling three quarters you use
GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY())
I dropped that into my full calculation and only got 0's, can you correct?
Rolling 4 Quarters =var _currentDate = TODAY()RETURNif(DATEDIFF(_currentDate, Date_Dim[CAL_DATE_YR_MTH_DAY_E], QUARTER) in GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY()),1,0)
