cancel
Showing results for
Did you mean:
gregmhoffmann 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) 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

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo 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
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! 3 REPLIES 3
LivioLanzo 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
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! gregmhoffmann Frequent Visitor

Re: Expense Forecasting From Static Excel Sheet

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

Kristjan76 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(
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
GENERATESERIES(0;_NumberOfPeriods);
"Test";EDATE(_NextDueDate;[Value]*_Frequency)
)
);
"Cost";'Expenses'[Cost];
"DueDate";[Test];
"Amount";'Expenses'[Amount]
)

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 43 members 892 guests
Recent signins:
• tblodget • andrewtlavoie • MLMarshall • • Gordonlilj • duggy • Nathaniel_C • Bielite • kylerligon • drewsk • Ashley_Finney • paulocorrea • martinezmachuca • oleg_rod 