cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shreyaspuranik
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: 

TransactionCategoryPost DateExpense
A11/1/2021$10
B21/4/2021$30
C21/4/2021$10
D31/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: 

TransactionCategoryPost DateExpense
A11/1/2021$10
  1/2/2021$0
  1/3/2021$0
B21/4/2021$30
C21/4/2021$10
  1/5/2021$0
  1/6/2021$0
D31/7/2021$15

 

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

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @shreyaspuranik ,

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')

vluwangmsft_0-1632292945093.png

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

vluwangmsft_1-1632293086920.png

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

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @shreyaspuranik ,

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')

vluwangmsft_0-1632292945093.png

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

vluwangmsft_1-1632293086920.png

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

View solution in original post

goncalogeraldes
Solution Supplier
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

@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!

shreyaspuranik_0-1631899107198.png

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!