topic Re: New table column with GenerateSeries() in DAX Commands and Tips

New table column with GenerateSeries()

Anonymous — Mon, 27 Apr 2020 14:24:51 GMT

Hello eveyone,

i'm new with DAX and i face to a problem.

I have a table from my SQL Server database where I have 3 columns BillingDate; Recurrence (Month / Year); periodicity (a number)

I'd like to generate a billing forecast for the next 3 years.

For that, I want to create a table column for each rows which contain all my next billing date occurencies. Is it possible?

----------------------------------------------------------------------------------------------------------
Ex:
Current billing date: 01/01/2020
Recurrence: Month
Periodicity: 3

I would like to generate a table column for the next 3 years like that:
04/01/2020; 07/01/2020; 10/01/2020; 01/01/2021; 04/01/2021; 07/01/2021; 10/01/2021; ....
----------------------------------------------------------------------------------------------------------

For that, i've generated a first new column 'MonthRecurrency' which calculate how many month I have to add basing to my 'Recurrence' column and my 'Periodicity' column.

To finish, I used the DAX function GenerateSeries() like this:

GENERATESERIES('MyTable'[BillingDate].[Date]; DATEADD('MyTable'[BillingDate].[Date]; 36; MONTH); MaTable[MonthRecurrency])

Yet, I have an error: "GenerateSeries arguments cannont be empty"

Am I on the good way, and if 'Yes', can you help me to find my mistake plz?

Re: New table column with GenerateSeries()

camargos88 — Mon, 27 Apr 2020 20:57:50 GMT

Hi Anonymous ,

I've created an example using Power Query, take a look on Advanced Editor.

File: Download PBIX

Ricardo

Re: New table column with GenerateSeries()

Anonymous — Tue, 28 Apr 2020 07:12:41 GMT

Hi camargos88 ,

First, thank you for replying.

if I correctly understand what you have done, you generated a column DateTime type for the forecast date?

But, in my case, I need to generate a list of date for each rows on a period.

If I take one row of your example:

Billing date: 01/01/2020

Recurrence: Month

Periodicity: 3

For this row, i'm waiting for a ForecastDate like this (a table): {04/01/2020; 07/01/2020; 10/01/2020; 01/01/2021; 04/01/2021; ...}

Is is possible to generate something like this for each rows of my tab?

Best regards,

Re: New table column with GenerateSeries()

camargos88 — Tue, 28 Apr 2020 10:33:36 GMT

Hi Anonymous ,

This code creates a range of dates, take a look at the steps on Power Query. First it get the recurrence for each row and generate a list of dates based on it. After that it expands the list on new rows, so the first 3 columns will be repeated with the new dates.

Do you need just the first date for each month or the range ?

Ricardo

Re: New table column with GenerateSeries()

Anonymous — Tue, 28 Apr 2020 10:43:54 GMT

Ok, i now understand what you did.

While doing a DateTime table column as I expect you duplicate each row with the forecast date I expected. It's clever.

I will try your adanced query on my project.

I will give you a feedback.

Thank again for the help

Re: New table column with GenerateSeries()

camargos88 — Tue, 28 Apr 2020 10:48:44 GMT

Anonymous ,

Doesn't work if you have a date table with all you dates ?

What do you want as result ?

Ricardo

Re: New table column with GenerateSeries()

Anonymous — Tue, 28 Apr 2020 10:55:20 GMT

Your reply seems logic and should work in my case, so what I expected don't matter.

Your solution seems to be better than mine.

As I said i'm new in Dax language so my option was probably not the good way to do such a thing.

Re: New table column with GenerateSeries()

camargos88 — Tue, 28 Apr 2020 11:39:40 GMT

Anonymous ,

If you consider it as a solution, please mark as a solution and kudos. So we can close this topic and help others.

Ricardo