Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |