topic Re: Generateseries with Dates in DAX Commands and Tips

Generateseries with Dates

mcguija — Thu, 11 Jan 2024 14:18:29 GMT

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,0)

Help!

thanks in advance.

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 14:26:55 GMT

That is four quarters.

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 14:25:20 GMT

thats fine, how about the issue with the end date

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 14:27:02 GMT

If you want rolling three quarters you use

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

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 14:30:24 GMT

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)

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 15:10:15 GMT

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

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 15:14:12 GMT

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

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 15:20:22 GMT

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)

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 15:27:41 GMT

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

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 15:29:39 GMT

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

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 15:45:29 GMT

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.

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 15:46:18 GMT

You need previous three quarters and current quarter to date?

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 16:14:04 GMT

Yes, that's correct.

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 18:52:32 GMT

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())

Re: Generateseries with Dates

mcguija — Thu, 11 Jan 2024 19:56:38 GMT

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"

Re: Generateseries with Dates

lbendlin — Thu, 11 Jan 2024 20:02:32 GMT

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)

Re: Generateseries with Dates

mcguija — Fri, 12 Jan 2024 14:06:22 GMT

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.

Re: Generateseries with Dates

lbendlin — Fri, 12 Jan 2024 14:12:09 GMT

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)

Re: Generateseries with Dates

mcguija — Fri, 12 Jan 2024 14:18:16 GMT

hmm, something is missing, throwing errors.

Re: Generateseries with Dates

lbendlin — Fri, 12 Jan 2024 14:33:26 GMT

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)