cancel
Showing results 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.

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.

Best Regards

Lucien

Impactful Individual

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.

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!

Impactful Individual

Hello there @shreyaspuranik ! What about:

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

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

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

Announcements

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.