Forum Discussion
New table column with GenerateSeries()
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?
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
7 Replies
camargos88Community Champion
Hi Anonymous ,
I've created an example using Power Query, take a look on Advanced Editor.
File: Download PBIX
Ricardo
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,
- camargos88
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