Actual by day vs Budget by month Analysis

[MaddyPena]

Hello guys!

I'm new with Power BI, and I'm trying to achieve a really common scenario.

I have experience with another tool that create relationships in a fairly easy way, but I'm not sure how to do this.  I have two spreadsheets, one with all account movements by day and the budget table, by month.

I had to pivot my budget table, like this:

to 

And created a first of month column on my "Account movements table"

With this, I can no longer create a relationship between the "IT Concept" column because I don't have the many to many option, so I created another IT Concept table. But my problem is that I cannot link the StartofMonth column, to my Date column on the pivot table, as it is a many to many relationship.

Do I need to create a fourth table with dates only to achieve this?

I'll really appreciate any help. Best regards!

[Phil_Seamark · Accepted solution]

Sounds like you need a DATE table, which I have in pretty much every data model I have ever built

You can do this easily enough in DAX by using the following command

Dates = CALENDARAUTO()

You can add columns to this table for other grouping (like, Months and Years etc)

[MaddyPena · Accepted solution]

I see, thanks!

So then I just create relationships to the Dates table from my data tables, right?

[Phil_Seamark · Accepted solution]

Sounds like you need a DATE table, which I have in pretty much every data model I have ever built

You can do this easily enough in DAX by using the following command

Dates = CALENDARAUTO()

You can add columns to this table for other grouping (like, Months and Years etc)

[MaddyPena · Accepted solution]

I see, thanks!

So then I just create relationships to the Dates table from my data tables, right?

[Phil_Seamark · Accepted solution]

Yeah, and you use this as the filter table.

The main thing is you can easily add columns to your date table like this :

MonthID = INT( FORMAT('Dates'[Date],"YYYYMM"))

and 

Month = FORMAT('Dates'[Date] , "MMM YY")

[MaddyPena]

Awesome, thanks!