Forecasting per quarter

Question

Hello,I have debt collections with a start and enddate. They have different frequencies like the following table:IDStartdateEnddateAmountFrequency11-1-201731-12-201910Monthly21-1-201731-12-201910Once31-1-201731-12-201910Yearly41-1-201731-12-201910Quarterly51-1-201731-12-2019102 months61-1-201731-12-201910Half year&nbsp;For this debt collections I need to calculate the forecast for 2019 (and further years) but this should be based on the frequency.&nbsp;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.&nbsp;&nbsp;Frequency&nbsp;Monthlyeach monthOnceOnce in month of startdateYearlyYearly in month of startdateQuarterlymonth of startdate + 3 months later etc.2 monthsMonth of startdate + 2 months later etc.Half yearMonth of startdate +&nbsp; 6 months later etc.&nbsp;So finally my forecast should be:&nbsp;&nbsp;OnceMonthly2 monthsQuarterlyHalf yearYearTotalJanuary10101010101060February&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;10March&nbsp;1010&nbsp;&nbsp;&nbsp;20April&nbsp;10&nbsp;10&nbsp;&nbsp;20May&nbsp;1010&nbsp;&nbsp;&nbsp;20June&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;10July&nbsp;10101010&nbsp;40August&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;10September&nbsp;1010&nbsp;&nbsp;&nbsp;20October&nbsp;10&nbsp;10&nbsp;&nbsp;20November&nbsp;1010&nbsp;&nbsp;&nbsp;20December&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;If you could help me please let me now!Regards,Jarno

liviolanzo · Accepted Answer

JarnoVisser&nbsp;You are going to need to modify your table with this DAX and create a new table:&nbsp;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:&nbsp;Data ModelResults&nbsp;where the measure total amount is just:&nbsp;Total Amount = SUM( 'Modified Data'[Amount] )&nbsp;let me know if you need to upload the pbix file

Forum Discussion

Forecasting per quarter

2 Replies

Related content

Forecasting with DAX

Forecasting

Quarter over Quarter calculation - Display Quarters missing

Live Weather Forecast Dashboard

Calculating Quarter over Quarter Change

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

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.

	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