Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhay03
Helper I
Helper I

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

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)

DIDDateMonthNumberMonthYear
511/25/201711Nov 2017
611/26/201711Nov 2017

 

DimCalendar – Copy (Unfiltered Copy of DimCalendar)

DIDDateMonthNumberMonthYear
109/24/20179Sep 2017
209/25/20179Sep 2017
310/15/201710Oct 2017
410/16/201710Oct 2018
511/25/201711Nov 2017
611/26/201711Nov 2017

 

FactCategory (Both Calendar tables joined with Date)

IDSalesDate
110009/24/2017
220009/25/2017
130010/15/2017
240010/16/2017
350011/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. 

 

5 REPLIES 5
sdjensen
Solution Sage
Solution Sage

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...

/sdjensen

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.

@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

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

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

/sdjensen

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.