Generateseries with Dates

[mcguija]

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.

[mcguija]

thats fine, how about the issue with the end date

[lbendlin]

If you want rolling three quarters you use

GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY())

[mcguija]

I dropped that into my full calculation and only got 0's, can you correct?

Rolling 4 Quarters =

var _currentDate = TODAY()

RETURN

if(DATEDIFF(_currentDate, Date_Dim[CAL_DATE_YR_MTH_DAY_E], QUARTER) in GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY()),1,0)

[lbendlin]

Don't drop it in, replace your code  completely.

Rolling 3 Quarters = GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY())

or use it as a TREATAS filter

[mcguija]

ok, I tried that and it threw an error - 'Table of multiple values was supplied where a single was expected'

[lbendlin]

if([CAL_DATE_YR_MTH_DAY_E] in GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY()),1,0)

or, simpler

if([CAL_DATE_YR_MTH_DAY_E]>EDATE(TODAY(),-9),1,0)

[mcguija]

This could work but theres a conflict between datatypes, date and text.  Can I convert one here in the calucation?

[lbendlin]

Pick the date column from your calendar table, or use DATEVALUE()

[mcguija]

ok, so using this:

Rolling 3 Quarters = if(DATEVALUE(Date_Dim[CAL_DATE_YR_MTH_DAY_E]) in GENERATESERIES(EDATE(TODAY(),-9)+1,TODAY()),1,0)

But it brings data back but its from 2023-04-12 --> 2024-01-11 aka today.

I need 2023-04-01 (3 quarters back) --> 2024-01-11 aka today.

[lbendlin]

You need previous three quarters and current quarter to date?

[mcguija]

Yes, that's correct.

[lbendlin]

Rolling prev 3 Quarters and current QTD = 
var q = QUARTER(EDATE(TODAY(),-9))
var y = YEAR(EDATE(TODAY(),-9))
return GENERATESERIES(DATE(y,q*3-2,1),TODAY())

[mcguija]

Hi, I think we are super close, now an error is thrown, I added your column and also broke it down to see variable values, see error below.

"a table of multiple values was supplied where a single value was expected"

[lbendlin]

Please be more precise in your requirement.

Column Flag For Rolling prev 3 Quarters and current QTD = 
var q = QUARTER(EDATE(TODAY(),-9))
var y = YEAR(EDATE(TODAY(),-9))
return if((Date_Dim[CAL_DATE_YR_MTH_DAY_E]) >= DATE(y,q*3-2,1),1,0)

[mcguija]

Hi, sadly this doesn't work either, it shows the correct start date of 2023-04-01 but the end date is way into the future - in fact all the was to the end of our date dim, where it should stop at todays date, 2024-01-12.  

[lbendlin]

. 

Column Flag For Rolling prev 3 Quarters and current QTD = 
var q = QUARTER(EDATE(TODAY(),-9)) 
var y = YEAR(EDATE(TODAY(),-9)) 
return if([CAL_DATE_YR_MTH_DAY_E]) >= DATE(y,q*3-2,1) && [CAL_DATE_YR_MTH_DAY_E]<= TODAY(),1,0)

[mcguija]

hmm, something is missing, throwing errors.

[lbendlin]

Column Flag For Rolling prev 3 Quarters and current QTD = 
var q = QUARTER(EDATE(TODAY(),-9)) 
var y = YEAR(EDATE(TODAY(),-9)) 
return if([CAL_DATE_YR_MTH_DAY_E] >= DATE(y,q*3-2,1) && [CAL_DATE_YR_MTH_DAY_E]<= TODAY(),1,0)

[mcguija · Accepted solution]

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)

[lbendlin]

That is four quarters.