Create dynamic 3 Months/ 6 Months/ 12 Months filter column

[abhay03]

In my DimCalendar table, there is a field MonthNumber for each date. I already have a report-level filter on Month-Year field. Now I have to add another report-level filter from the unfiltered copy of DimCalender with values "Last 3 Months", "Last 6 Months", "Last 12 Months" to show the trend. Below is the sample data for the explanation:

DimCalendar (Report-level filtered on Current Month)

DID	Date	MonthNumber	MonthYear
5	11/25/2017	11	Nov 2017
6	11/26/2017	11	Nov 2017

DimCalendar – Copy (Unfiltered Copy of DimCalendar)

DID	Date	MonthNumber	MonthYear
1	09/24/2017	9	Sep 2017
2	09/25/2017	9	Sep 2017
3	10/15/2017	10	Oct 2017
4	10/16/2017	10	Oct 2018
5	11/25/2017	11	Nov 2017
6	11/26/2017	11	Nov 2017

FactCategory (Both Calendar tables joined with Date)

ID	Sales	Date
1	100	09/24/2017
2	200	09/25/2017
1	300	10/15/2017
2	400	10/16/2017
3	500	11/25/2017

For one of the visual in page, I want to add a column dynamically on DimCalendar - Copy table of "Last 3 Months", "Last 6 Months", "Last 12 Months" to show trends filter based on the selected month from the DimCalendar table. how this can be achieved as months in last 3 month will also be part of last 6 months and similarly for last 12 months. This calculation needs to be done in column to be able to use as a filter and with restrictions of direct query. 

[sdjensen]

Hi,

I think you should try to read this article by @cwebb - I think it will give you exactly what you want.

https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi...

[abhay03]

Hi @sdjensen, this is a good article but my data source is in Azure SQL Database connected using DirectQuery mode. In DirectQuery mode, it does not allow me to create a table.

[Greger1337]

@abhay03

Here is a example in T-SQL

ALTER PROCEDURE [dbo].[usp_DimDatePeriods]
	
	AS

  
    BEGIN 
	


	IF OBJECT_ID('dbo.DatePeriods', 'U') IS NOT NULL 
  DROP TABLE dbo.DatePeriods; 

  CREATE TABLE [dbo].DatePeriods (
    	[Date] [datetime] NOT NULL
    	,[Range] [char](50) NULL
    	,[Sort] [smallint] NULL
    	);

	DECLARE @useDate [datetime]
	SET @useDate = GETDATE()

		while @useDate = GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
			VALUES(  @useDate
               ,'Today',
			   1)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end
	SET @useDate = dateadd(week, datediff(week, 0, getdate()), 0)

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Current week to date',
			   2)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end

   SET @useDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Current month to date',
			   3)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end

 

   SET @useDate = DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Current year to date',
			   4)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end

	SET @useDate = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))

		while DATEPART(MONTH,@useDate) = DATEPART(MONTH,DATEADD(month, -1, GETDATE()))
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Previous month',
			   5)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end

	SET @useDate = DATEADD(DAY,-6,GETDATE())

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Rolling week',
			   6)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end

	SET @useDate = DATEADD(day,1,DATEADD(month,-1,GETDATE()))

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Rolling month',
			   7)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end
	SET @useDate = DATEADD(day,1,DATEADD(year,-1,GETDATE()))

		while @useDate <= GETDATE()
		begin
			INSERT INTO DatePeriods
               (Date
               ,Range
			   ,Sort
				)
           VALUES(  @useDate
               ,'Rolling year',
			   8)
               
			set @useDate = DATEADD(DAY,1,@useDate);
		end
	END

[Greger1337]

Create the table mentioned in the article with a scheduled stored procedure in your SQL database to update it one every day.

[sdjensen]

@abhay03 - then I am not sure you will be able to handle this in one column.