Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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!
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!
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]
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |