Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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')
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
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')
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
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!
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |