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

Proud to be a Datanaut!

3 REPLIES 3
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]
)```

Proud to be a Datanaut!

Frequent Visitor

## Re: Expense Forecasting From Static Excel Sheet

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

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.

#### Community News & Announcements

Get your latest community news and announcements.

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