Forum Discussion

mcguija's avatar
mcguija
Helper III
2 years ago
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-...
  • mcguija's avatar
    mcguija
    2 years ago

    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)
mcguija's avatar
mcguija
Helper III
2 years ago

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's avatar
lbendlin
Super User
2 years ago

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's avatar
    mcguija
    Helper III
    2 years ago

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

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

       

      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's avatar
        mcguija
        Helper III
        2 years ago

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