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

 

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

3 Replies

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