How to arrange the dates?

[Danhartzi]

Hey guys.
I have two tables.

One - showing products yearly budget when each column represents the related month:

Two - showing products yearly actual expenses when each line has its own month (of course summed up with sumx to show sums):

Showing YTD to the second table is simple using SUMX because the only lines I will have are the actuals months that include expenses in. But how do I do YTD to the first table? How can I give it a reference to the month we are at right now?

Thank you in advance !

[Wilson_]

Hello Dan,

The simplest solution would be to unpivot the first table (ideally in the original data source, but also possible in Power Query). That way, you will end up with your data in the same format as the second table. Power BI generally works better with tall (fewer columns, more rows) tables than wide (more columns, fewer rows) tables anyway.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Danhartzi]

Hey Wilson and thank you for the quick reply !

Trying to further explain my problem here..
The budget (first table) as seen has columns for each P (months) but also has a internal ID system as follows:

As you can see the 4th column takes the CC first, adds the Product name and then adds the GL.
This creates a unique Internal ID so that I can connect those tables as seen here :

If I unpivote this unique column would no longer be unique since every single Internal ID now has a few rows to it (for each P/month).

Any other ideas maybe to solve this issue?

Thank you !
Daniel

[Wilson_ · Accepted solution]

Hey Daniel,

Ah I see, you also have a second data modeling issue, in my opinion: you're creating a relationship between two fact tables. (Additional reading about star schemas if you're interested in the data modeling. A bit lengthy but SQLBI is my go-to for Power BI things.). Here is a super small mockup of what I would do instead.

What I have is the budget table unpivoted, then a calculated table with all the unique combinations of the internal key (with a few other columns you might want to work with; feel free to remove or add columns of course). That calculated table is the "dimension table" that has the one-to-many relationship with both the actual and budget tables. I also threw in a sample Calendar table.

This is what my data model looks like:

This is what my visual looks like:

Hopefully that works for you. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)