Forum Discussion
Personal project need help to get started
Hi, I'm new to the forum and looking to start my first project with power bi! I hope to learn and hopefully get to a point where I can share knowledge in the future.
I want to get some practical experience with a real outcome, so I picked my personal expenses as my project. I started on it this week and struggled with creating measures such as comparing this quarter vs last quarter grocery expenses, or avg of last 3 months. Every time I tried a DAX formula online, I got an error or blank values. I think I'm doing something wrong and I am wondering if I have a bad data model where measures can't be created, at least that is where I'm thinking at this time.
To provide an example of the data I'm working with, here is a sample with arbitary numbers of transactions of income, salary, expenses in one excel file. I imported the data into power bi straight from my file, so it has only one table. The table has income and expenses, with categories to provide me more detail.
So my question is should I be separating the transaction in different ways? If so, what types of tables should I set up for the data model to work for me? How would you go about this?
I feel kind of stupid that I am challenged and I am not sure what I am doing wrong.
| Date | Category | Item | Trans | Description | Amount |
| 2020-01-15 | home | mortgage | withdraw | 1400 | |
| 2020-01-15 | income | pay | deposit | 800 | |
| 2020-01-31 | income | pay | deposit | 800 | |
| 2020-02-14 | transportation | transit | credit | 50 | |
| 2020-02-15 | home | mortgage | withdraw | 1400 | |
| 2020-02-15 | income | pay | deposit | 800 | |
| 2020-02-28 | income | pay | deposit | 800 | |
| 2020-03-01 | non essential | misc | cash | 10 | |
| 2020-03-15 | home | mortgage | withdraw | 1400 | |
| 2020-03-15 | income | pay | deposit | 800 | |
| 2020-03-29 | income | pay | deposit | 800 | |
| 2020-03-30 | income | pay | deposit | 800 | |
| 2020-04-01 | essential | grocery | debit | 100 | |
| 2020-05-01 | non essential | electronic | credit | 85 | |
| 2020-05-01 | utilities | hydro | withdraw | 85 | |
| 2020-05-01 | utilities | water | withdraw | 80 | |
| 2020-05-01 | utilities | gas | withdraw | 50 |
I accomplished the foundations of my projects in a day today after I looked into the data schema and implementing it in power bi. So I ended up with three tables, and I believe it made it easier to compare income vs expenses and set up some measures. It was pretty cool when I got the first one, and the next ones were easier to create once I got the hang of it. I'm pleased with the progress from today but I know there is much more I need to learn and understand.
It was a struggle to get anything to work before posting here on the forum (it was frustrating), so I'm truly appreciative of the help offered by the posters above to lead me in the right direction.
5 Replies
- Greg_Deckler
Community Champion
That looks pretty solid as a fact table. What you are probably lacking are dimension tables. To use the time intelligence functions, you will need a separate calendar table. I suggest creating one with CALENDARAUTO. The time intelligence functions will drive you crazy without one and even with one, eh, you have a 50/50 chance of getting what you want. If you want explicit control over how you slice and dice things, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for. https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Also, read this just because it is a good thing to read if you are new to the forums: How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
@ people if you want to reference them, kudo people that help you, welcome and let me know what I can do to assist datsright
- amitchandak
Super User
datsright , You are going the right way. But prefer a start schema and date table,
refer
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
https://www.sqlshack.com/implementing-star-schemas-in-power-bi-desktop/
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ I will need to do a bit more research on the star schema.
I'm going to try making the date table (thanks for the advice there). Should I create more tables/dimensions from my fact table? This is where I'm currently stuck.
I accomplished the foundations of my projects in a day today after I looked into the data schema and implementing it in power bi. So I ended up with three tables, and I believe it made it easier to compare income vs expenses and set up some measures. It was pretty cool when I got the first one, and the next ones were easier to create once I got the hang of it. I'm pleased with the progress from today but I know there is much more I need to learn and understand.
It was a struggle to get anything to work before posting here on the forum (it was frustrating), so I'm truly appreciative of the help offered by the posters above to lead me in the right direction.
- v-easonf-msft
Community Support
Hi , datsright
Glad to hear that you have worked out the problem by yourself .
Could you please please mark the helpful replies or add your reply as Answered to close this thread.
Best Regards,
Community Support Team _ Eason
