cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gregmhoffmann Frequent Visitor
Frequent Visitor

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)

expense 1.PNG

 

 

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:

 

expense 2.PNG

 

Thanks

Greg

 

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Expense Forecasting From Static Excel Sheet

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

3 REPLIES 3
LivioLanzo Super Contributor
Super Contributor

Re: Expense Forecasting From Static Excel Sheet

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

gregmhoffmann Frequent Visitor
Frequent Visitor

Re: Expense Forecasting From Static Excel Sheet

@LivioLanzo 

 

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

Kristjan76 Member
Member

Re: Expense Forecasting From Static Excel Sheet

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 43 members 892 guests
Please welcome our newest community members: