Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.