Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors