Expense Forecasting From Static Excel Sheet

[gregmhoffmann]

Hey Everyone!

I have an Excel workbook that has a list of expenses, what day of the week they are due, and the frequency of the expense in months (shown below)

Is there a way to create a new table in Power BI that has a list of dates with the expenses due on that date for up to a year out?  For example, since today is 11/26/2018, the table would show all expenses due up till 11/26/2019.

Example of what I'm hoping to achieve:

Thanks

Greg

[LivioLanzo · Accepted solution]

Hello @gregmhoffmann

Please check if this query does what you want

Table =
SELECTCOLUMNS (
    GENERATE (
        Expenses,
        VAR __DayofMonth = Expenses[Due Date (Day of Mo)]
        VAR __StartDate = Expenses[Start Due Date]
        VAR __Frequency = Expenses[Frequency (in Mo)]
        VAR SameMonthDueDate =
            DATE ( YEAR ( __StartDate ), MONTH ( __StartDate ), __DayofMonth )
        VAR NextMonthDueDate =
            EDATE ( SameMonthDueDate, 1 )
        VAR FirstDueDate =
            IF ( SameMonthDueDate <= __StartDate, NextMonthDueDate, SameMonthDueDate )
        VAR NumberOfPeriods =
            ROUNDUP (
                DATEDIFF ( __StartDate, EDATE ( TODAY (), 12 ), MONTH ) / __Frequency,
                0
            )
        RETURN
            ADDCOLUMNS (
                GENERATESERIES ( 1, NumberOfPeriods ),
                "Test", EDATE ( FirstDueDate, ( [Value] - 1 ) * __Frequency )
            )
    ),
    "Expense", [Expense],
    "Payment Amount", [Payment Amount],
    "Date", [Test]
)

[Anonymous]

I found this awesome and very useful, with this version the next due depends on the start date month. For example if you have due dates every 6 months and it always happens in June and December.

Forcasting Cost =
SELECTCOLUMNS(
    GENERATE(
        'Expenses';
        VAR _FirstDate = EOMONTH(TODAY();-1)+1
        VAR _DayOfMonth = 'Expenses'[Due day in month]
        VAR _StartDate = 'Expenses'[First due date]
        VAR _ThisMonthDate = DATE(YEAR(_FirstDate);MONTH(_FirstDate);_DayOfMonth)
        VAR _Frequency = 'Expenses'[Frequency in months]
        VAR _NextDueDate =
            MINX(
                FILTER(
                    ADDCOLUMNS(
                        GENERATESERIES(
                            _ThisMonthDate;
                            EDATE(_ThisMonthDate;5*12)
                        );
                        "DueDate";
                            MOD(DATEDIFF(_StartDate;[Value];MONTH);_Frequency) = 0
                            && EDATE(_StartDate;DATEDIFF(_StartDate;[Value];MONTH))=[Value]
                    );
                    [DueDate]=TRUE()
                );
                [Value]
            )
       
        VAR _NumberOfPeriods =
            ROUNDUP(DIVIDE(12*2;_Frequency);0)-1
           
        RETURN
            ADDCOLUMNS(
                GENERATESERIES(0;_NumberOfPeriods);
                "Test";EDATE(_NextDueDate;[Value]*_Frequency)
            )
    );
    "Cost";'Expenses'[Cost];
    "DueDate";[Test];
    "Amount";'Expenses'[Amount]
)

[gregmhoffmann]

@LivioLanzo 

Thank you so much!  That worked right off the bat!