Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi,
I think you should try to read this article by @cwebb - I think it will give you exactly what you want.
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.
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |