Forum Discussion
two tables with 2 dates, create relative month
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.
Solution SageOk 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.
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.