topic Re: Personal Budget Date Joins - Actual and Budget in DAX Commands and Tips

Personal Budget Date Joins - Actual and Budget

CatManKuhn — Sat, 13 Feb 2021 04:04:07 GMT

Hi everyone,

Hoping someone can assist. I am working on building a personal budget as I am closing on a house. I currently have four sheets in a Google Sheet which are Income, Expenses, Budget, and Category. These are all imported into Power BI. I also have a Date table in Power BI.

https://docs.google.com/spreadsheets/d/e/2PACX-1vRZ7QVpINSwtnHJDT2G4CzZEyOK10L1ooyKLGdjP5wjEIYRtrsXXeMQPqLNRgbd_eDfmnsT89VcQ-OJ/pubhtml

I want to be able to see the actual and budgeted expense for each category by date hierarchy in a matrix. The issue is that my budget values are updated randomly. For example, I may budget $30 for subscriptions on 1/1/2020 until I subscribe to a new one. I would then effective date the change and log the new budgeted price, say $50 on 4/1/2020. Given that it is currently 2/12/2020 the subscription budget should show $50 for every month in the past until 4/1/2020, then it should show $30.

When creating measures, I also want the values to return the budgeted price at that date rather than summarize all values for Subscriptions regardless of date.

I hope I communicated this well as I have really been struggling. Please let me know of any questions and thank you in advance for your time.

Re: Personal Budget Date Joins - Actual and Budget

rfigtree — Sat, 13 Feb 2021 09:28:57 GMT

Curious about why you are doing this, unless its for fun or to learn something. Is analagous to using the USS Missouri to dislodge an old man who is on the beach armed only with a small stick.

You might consider using power query to build up your budget table, it has a great feature fill up and down which would be handy in your situation. will need to do a bit of fancy foot work but it will do the job.

Re: Personal Budget Date Joins - Actual and Budget

CatManKuhn — Sat, 13 Feb 2021 16:12:25 GMT

Thanks. I am doing this to keep track of my personal finances. Is there another way to go about doing this?

Re: Personal Budget Date Joins - Actual and Budget

rfigtree — Sat, 13 Feb 2021 22:05:03 GMT

just use native excel, there is a BIG BIG BIG learing curve to using the power tools, takes a long time to grasp the concepts. as the experts say it is easy but very hard.

the power tools are really for profesional data analysts (not trying being condescending) who are doing repetative/complex analysis of typically(not always) large data sets.

Re: Personal Budget Date Joins - Actual and Budget

CatManKuhn — Sun, 14 Feb 2021 04:25:54 GMT

I appreciate your response. I am a professional data analyst and use Power BI for data modeling and reporting at a large company. I like to use the program to better inform myself of my habits; in this case finances. I have also created models and reports that track the progress of albums I listen to during 2021. An added benefit to doing these little projects is that it furthers my development and my understanding of Power BI.

I have solved my issue and my report works great. I now have a dynamic budget with monthly and YTD visuals.

Re: Personal Budget Date Joins - Actual and Budget

rfigtree — Sun, 14 Feb 2021 04:41:22 GMT

Out of curiosity what was the solution to your original problem?

Re: Personal Budget Date Joins - Actual and Budget

CatManKuhn — Wed, 07 Apr 2021 15:08:42 GMT

Hi rfigtree!

Apologies for the very delayed response. My solution was to create a Start Date and End Date for each budget category. Here is a screenshot:

I then feed this into Power Query and used a custom column to fill the series. In order to do this I first formated the date as a whole number. The formula looks like this:

I then expanded the new column and then cleaned up my query.

Was this overkill? Probably, but I was able to solve the issue while also learning more about the capabilities in Power BI. I also learned I spend a lot of money on my house.