Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I have debt collections with a start and enddate. They have different frequencies like the following table:
ID | Startdate | Enddate | Amount | Frequency |
1 | 1-1-2017 | 31-12-2019 | 10 | Monthly |
2 | 1-1-2017 | 31-12-2019 | 10 | Once |
3 | 1-1-2017 | 31-12-2019 | 10 | Yearly |
4 | 1-1-2017 | 31-12-2019 | 10 | Quarterly |
5 | 1-1-2017 | 31-12-2019 | 10 | 2 months |
6 | 1-1-2017 | 31-12-2019 | 10 | Half year |
For this debt collections I need to calculate the forecast for 2019 (and further years) but this should be based on the frequency.
The debt collection will take place in the month of the startdate + the frequency. So yearly will take place in january 2019. Quarterly in january, april, etc.
Frequency | |
Monthly | each month |
Once | Once in month of startdate |
Yearly | Yearly in month of startdate |
Quarterly | month of startdate + 3 months later etc. |
2 months | Month of startdate + 2 months later etc. |
Half year | Month of startdate + 6 months later etc. |
So finally my forecast should be:
Once | Monthly | 2 months | Quarterly | Half year | Year | Total | |
January | 10 | 10 | 10 | 10 | 10 | 10 | 60 |
February | 10 | 10 | |||||
March | 10 | 10 | 20 | ||||
April | 10 | 10 | 20 | ||||
May | 10 | 10 | 20 | ||||
June | 10 | 10 | |||||
July | 10 | 10 | 10 | 10 | 40 | ||
August | 10 | 10 | |||||
September | 10 | 10 | 20 | ||||
October | 10 | 10 | 20 | ||||
November | 10 | 10 | 20 | ||||
December | 10 | 10 |
If you could help me please let me now!
Regards,
Jarno
Solved! Go to Solution.
You are going to need to modify your table with this DAX and create a new table:
Modified Data = SELECTCOLUMNS( GENERATE( Data, VAR Freq = Data[Frequency] VAR NumberOfPeriods = SWITCH( Freq, "Monthly", DATEDIFF( Data[Startdate], Data[Enddate], MONTH ), "2 Months", INT( DATEDIFF( Data[Startdate], Data[Enddate], MONTH ) / 2 ), "Quarterly", DATEDIFF( Data[Startdate], Data[Enddate], QUARTER ), "Half Year", INT( DATEDIFF( Data[Startdate], Data[Enddate], MONTH ) / 6 ), "Yearly", DATEDIFF( Data[Startdate], Data[Enddate], YEAR ), "Once", 0 ) RETURN GENERATESERIES( 0, NumberOfPeriods ) ), "ID", [ID], "Amount", [Amount], "Frequency", [Frequency], "Date", SWITCH( [Frequency], "Monthly", EDATE( [Startdate], [Value] ), "2 Months", EDATE( [Startdate], [Value] * 2 ), "Quarterly", EDATE( [Startdate], [Value] * 3 ), "Half YEar", EDATE( [Startdate], [Value] * 6 ), "Yearly", EDATE( [Startdate], [Value] * 12 ), "Once", [Startdate] ) )
Then you are able to build this simple model and get the following results:
where the measure total amount is just:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
You are going to need to modify your table with this DAX and create a new table:
Modified Data = SELECTCOLUMNS( GENERATE( Data, VAR Freq = Data[Frequency] VAR NumberOfPeriods = SWITCH( Freq, "Monthly", DATEDIFF( Data[Startdate], Data[Enddate], MONTH ), "2 Months", INT( DATEDIFF( Data[Startdate], Data[Enddate], MONTH ) / 2 ), "Quarterly", DATEDIFF( Data[Startdate], Data[Enddate], QUARTER ), "Half Year", INT( DATEDIFF( Data[Startdate], Data[Enddate], MONTH ) / 6 ), "Yearly", DATEDIFF( Data[Startdate], Data[Enddate], YEAR ), "Once", 0 ) RETURN GENERATESERIES( 0, NumberOfPeriods ) ), "ID", [ID], "Amount", [Amount], "Frequency", [Frequency], "Date", SWITCH( [Frequency], "Monthly", EDATE( [Startdate], [Value] ), "2 Months", EDATE( [Startdate], [Value] * 2 ), "Quarterly", EDATE( [Startdate], [Value] * 3 ), "Half YEar", EDATE( [Startdate], [Value] * 6 ), "Yearly", EDATE( [Startdate], [Value] * 12 ), "Once", [Startdate] ) )
Then you are able to build this simple model and get the following results:
where the measure total amount is just:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you Livio!
This is exactly what I needed, it works perfect!
Regards,
Jarno
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
101 | |
94 | |
38 | |
30 |