two tables with 2 dates, create relative month

[1241pm]

Hi,

I have two tables names Projection 1 and Projection 2.  The tables both have dates and sales values. Projection 1's dates range from September 2016 - December 2018 while Projection 2 goes from September 2017-December 2019. How do I create a column/measure that creates a relative date for both tables so when I graph, September 2016 and September 2017 are compared side by side? It does not matter if I  have to create a new column where Sep 2016/2017=1, Oct 2016/2017 = 2, etc as  they can be compared as the example modeled below. Thank you so much for your time. 

[Anonymous]

Are you seeking a chart to show the information as below? Use a single time axis to show values from different items?

If this is the case, you need to create a separate calendar and then create relathionships between the calendar tables with Projection 1 and Projection 2.

For creating a calendar, you can new a Table in PowerBI and using the following DAX.

Calendar = CALENDAR(DATE(2016,1,1),DATE(2019,12,31))

[1241pm]

Thanks for your reply. And not quite. Since they have irregular dates, the lines will start at different times. I need them to both start at the same time so I can compare the 2 projections. 

[Seward12533 · Accepted solution]

Ok I misunderstood so you want to plot Relative Months along with X Axis and then Compare Sept 17 data to Sept 18.  Assuming you have a Date field in your data you can add a column and use DATEDIFF.

You could add a shifted date column to calcuate the date 1 year earlier in Shifted Date = DATE(YEAR([date])-1,Month([date],DAY([date])) and then bridge that to your date table vs Date and then plot as @Anonymous suggested. 

Or Alternatively you could implement your Relative Date method with somethign like this. 

First Write a Measure to calculat the first Month for each Table

• Proj1 Start Month = CALCULATE(MONTH(MIN(proj1[Date])),ALL(Proj1))
• Proj2 Start Month = CALCULATE(MONTH(MIN(proj2[Date])),ALL(Proj2))

Then add a column to each for Relative Month 

• Relative Month = DATEDIFF([Proj1 Start Month],[Date],month)
• Similar column in Proj2 using [Proj2 Start Month] measure

Then you need a bridge table for the Relative Months.  

[1241pm]

thanks. alternatively, is there a measure or a way to create a column in power bi that I could use to calculate my dates in order into a numerical number. For example Sep 2016=1 , Oct 2016=2 ... Dec 2018=28.. This would make things easier and I could graph the x axis as this number for month 1, 2,...28, considering it is a 28 month projection. 

[Seward12533]

That is exactly what my Alternate Method using DATEDIFF does - returns the number of months since the base (earliest month) in the data set. 

[Seward12533]

You need to add a DATE column to calcualte a Calendar Month for the RelativeProjMonth.  Then use a date table to relate the two tables and write measures to calcuate the projections for each and then display those by Month from date table.