locked
How to forecast a straight-line average in DAX? RRS feed

  • Question

  • I developed a Power BI report where I need to chart the Net Payments per day for the current month (May 2017).  So I want to plot the actual data for the days that have transpired and I want to forecast the remaining days of this month according to a straight-line average. 

    Numerator = CALCULATE(SUM(Intra_Month_Cash_Pacing[net_payment_amount])*[Inflate_Factor],ALLSELECTED(Intra_Month_Cash_Pacing),Intra_Month_Cash_Pacing[MM]=MONTH(TODAY()),Intra_Month_Cash_Pacing[YY]=2017)
    
    Inflate_Factor = 1.47
    
    Total Days in Month = CALCULATE(MAX(Intra_Month_Cash_Pacing[max_posting_day_of_MM]),ALL(Intra_Month_Cash_Pacing),Intra_Month_Cash_Pacing[MM]=MONTH(TODAY()),Intra_Month_Cash_Pacing[YY]=2017)
    
    Slope = Numerator / [Total Days in Month]

    I am using the built-in Line Chart visual to forecast.  But the problem is that this visual does not allow me to programmatically set the forecast length.  So I am hard-coding the length based on the number of days remaining--not good.  I want to plot the above straight-line slope for the days following the last day of actual data.  (The last day of data is 15 since this is plotting posting days and the total posting days in this month is 22).  Here is the formula I'm using to plot the actual / transpired amounts:

    MTD 2017 Net-P = 
    IF(ISBLANK(Intra_Month_Cash_Pacing[2017 MTD]),BLANK(), 
    CALCULATE(
    	'Intra_Month_Cash_Pacing'[2017 MTD],
    	FILTER(
    		ALLSELECTED('Intra_Month_Cash_Pacing'[Posting_Day_DD]),
    		ISONORAFTER('Intra_Month_Cash_Pacing'[Posting_Day_DD], MAX('Intra_Month_Cash_Pacing'[Posting_Day_DD]), DESC)
    	)
    ))

    As you can see, this calculates all future values as Blanks, so that I can forecast these amounts instead.  But again, I need a dynamic way to select the forecast length.  How can I do this using DAX or another visual?

    Here is how my data looks for your reference:


    Ryan D


    • Edited by ironryan77 Monday, May 22, 2017 8:50 PM
    Monday, May 22, 2017 8:46 PM

Answers

  • Hi Ryan,

    Thanks for your question.

    According to your description, your problem is more related to PowerBI. Since our forum is discussing Power Pivot in Excel issue, To solve your question more efficiently, please post your question in PowerBI forum: http://community.powerbi.com ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, May 25, 2017 5:51 AM

All replies

  • Tuesday, May 23, 2017 1:02 AM
  • Thank you, this looks great.  But how did you calculate the K values?  I am using Power BI, not PowerPivot.  Is there a way to calculate these K values with DAX?  Also, since this is a dynamic report, I don't think I can use these same K values for every slicer combination.

    Ryan D

    Tuesday, May 23, 2017 1:27 PM
  • Oodles of curve fitting programs with Excel compatibility are available.
    I used the basic
    https://www.curveexpert.net/

    Tuesday, May 23, 2017 11:16 PM
  • Again, I need a solution that works with Power BI, not Excel.

    Ryan D

    Wednesday, May 24, 2017 1:36 PM
  • Hi Ryan,

    Thanks for your question.

    According to your description, your problem is more related to PowerBI. Since our forum is discussing Power Pivot in Excel issue, To solve your question more efficiently, please post your question in PowerBI forum: http://community.powerbi.com ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, May 25, 2017 5:51 AM