cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JarnoVisser
Helper I
Helper I

Forecasting per quarter

Hello,


I have debt collections with a start and enddate. They have different frequencies like the following table:

IDStartdateEnddateAmountFrequency
11-1-201731-12-201910Monthly
21-1-201731-12-201910Once
31-1-201731-12-201910Yearly
41-1-201731-12-201910Quarterly
51-1-201731-12-2019102 months
61-1-201731-12-201910Half 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 
Monthlyeach month
OnceOnce in month of startdate
YearlyYearly in month of startdate
Quarterlymonth of startdate + 3 months later etc.
2 monthsMonth of startdate + 2 months later etc.
Half yearMonth of startdate +  6 months later etc.

 

So finally my forecast should be:

 

 OnceMonthly2 monthsQuarterlyHalf yearYearTotal
January10101010101060
February 10    10
March 1010   20
April 10 10  20
May 1010   20
June 10    10
July 10101010 40
August 10    10
September 1010   20
October 10 10  20
November 1010   20
December 10    10

 

If you could help me please let me now!

Regards,

Jarno

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
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 ModelData ModelResultsResults

 where the measure total amount is just: 

Total Amount = SUM( 'Modified Data'[Amount] )
 
let me know if you need to upload the pbix file

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
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 ModelData ModelResultsResults

 where the measure total amount is just: 

Total Amount = SUM( 'Modified Data'[Amount] )
 
let me know if you need to upload the pbix file

 


 


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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.