Using TREATAS for different granularity levels

Hi AndreaLorenzo 

If you want to visualise the comparisons on a line graph for example with Month in the x Axis, then your Budget table will need to be broken down into months.

In Power Query you can do the following in your Goals Table for a monthly overview. 

Create a custom column to get a monthly amount

[Pending_Goal] / 12

Create another column called DateKey and Enter

"01/01/" & [Year]

convert to Date

Create a custom column called intervals with this formula

List.Numbers(1,12)

Expand the column

Now we need to create a date column for each interval called Month

Date.StartOfMonth(
  Date.AddMonths(
    [DateKey],
    [Intervals] -1
  )
))

Convert to date

You will have a monthly amount for each month of the year

Load the table and create a one to many relationship from the date table to the Month column in Goal table

Create a measure to  calculate the Goal amount 

Pending Goal = SUM('Goals'[MonthlyAmount]

Create a similar Measure from your Sales table and add the Dates from your date table to a visual and then the two measures and you will then get a comparison.

These measures will be the basis of all your measures.

Thanks

Joe

If you found my answer helpful and it solved your issue, please accept as solution