Forum Discussion

gregmhoffmann's avatar
gregmhoffmann
Frequent Visitor
7 years ago
Solved

Expense Forecasting From Static Excel Sheet

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 crea...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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]
)
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

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]
)
  • gregmhoffmann's avatar
    gregmhoffmann
    Frequent Visitor
    7 years ago

    LivioLanzo 

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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]
    )