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

Filling missing dates to track personal finance

Hi, I am trying to use power bi to track my personal finances and learn more about power bi by doing these small personal projects. When I download by bank statements, I get data in this format:

 Transaction Category Post Date Expense A 1 1/1/2021 \$10 B 2 1/4/2021 \$30 C 2 1/4/2021 \$10 D 3 1/10/2021 \$15

to get a trendline for my expected expenses in the future, I need to have equally spaced transaction dates and I believe i need data in this format:

 Transaction Category Post Date Expense A 1 1/1/2021 \$10 1/2/2021 \$0 1/3/2021 \$0 B 2 1/4/2021 \$30 C 2 1/4/2021 \$10 1/5/2021 \$0 1/6/2021 \$0 D 3 1/7/2021 \$15

How do I do this in power query ? Thanks in advance for your help!

1 ACCEPTED SOLUTION
Community Support

Test the below steps,

Step1,create a calander date table:

``Calendar = CALENDAR("2021,1,1","2021,1,10")``

Then use the following dax to create another table：

``Table 2 = NATURALLEFTOUTERJOIN('Calendar','Table')``

``Newexpense = IF('Table 2'[Expense]=BLANK(),0,'Table 2'[Expense])``

If you need to, you can extract some of the fields from this last table and create a report with only the fields you need to use.

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

Best Regards

Lucien

4 REPLIES 4
Community Support

Test the below steps,

Step1,create a calander date table:

``Calendar = CALENDAR("2021,1,1","2021,1,10")``

Then use the following dax to create another table：

``Table 2 = NATURALLEFTOUTERJOIN('Calendar','Table')``

``Newexpense = IF('Table 2'[Expense]=BLANK(),0,'Table 2'[Expense])``

If you need to, you can extract some of the fields from this last table and create a report with only the fields you need to use.

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

Best Regards

Lucien

Solution Supplier

Hello there @shreyaspuranik ! For a trend analysis you a need a date field with continuous values and to achieve this you can create a date table and connect it to your current table via the Date column. Here is a link on how to easily create a Date table.

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Frequent Visitor

@goncalogeraldes Thanks for your answer! I am trying to visualize my expenses in a line chart with date hierarchy and thus get some analytics like forecast and average expense (per unit in date hierarchy). Even with using a continious date field like you mentioned above, the average ( per unit day) is still calculated as total expense/ countdistinct(post date) instead of total expense/total days till today.  The red line is sum of expenses on each day. My question essentially stemmed from  a wrong average value and thus needing to create \$0 transactions for every day when there is no transaction. I hope I am making sense!

Solution Supplier

Hello there @shreyaspuranik ! What about:

``````measure =
var _days = DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), DAY )

return
DIVIDE( total expense, _days)``````

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Helpful resources

Announcements

Launching new user group features

Learn how to create your own user groups today!

Check it Out!

Click here to read more about the November 2021 Updates!

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors