Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
@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
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,
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
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
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.
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |