cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Forecasting per quarter

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

1 ACCEPTED SOLUTION
Solution Sage

@JarnoVisser

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:

Data ModelResults

where the measure total amount is just:

Total Amount = SUM( 'Modified Data'[Amount] )

let me know if you need to upload the pbix file

Proud to be a Datanaut!

2 REPLIES 2
Solution Sage

@JarnoVisser

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:

Data ModelResults

where the measure total amount is just:

Total Amount = SUM( 'Modified Data'[Amount] )

let me know if you need to upload the pbix file

Proud to be a Datanaut!

Helper I

Thank you Livio!

This is exactly what I needed, it works perfect!

Regards,

Jarno

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors