Answers
Nov 29, 2022 - 01:59 PM
There are a couple of ways to get around this:
1 - Create a Global Variable set to [[Current_Year]]-10-25 - This would have to be maintained ie changed EVERY Month
![]()
2 - Create an additional view called vwPREVIOUS_CUT_OFF in your database that generates the PREVIOUS_CUT_OFF_DATE. Then JOIN from your FACT table to vwPREVIOUS_CUT_OFF on FACT_Date >= PREVIOUS_CUT_OFF_DATE
If you then create a chart that includes PREVIOUS_CUT_OFF_DATE it will be limited to data up to the cut off, whereas creating a chart that does not include PREVIOUS_CUT_OFF_DATE will show all the data.
SQL SERVER Syntax for creating a view with a PREVIOUS_CUT_OFF_DATE set to 25th of the previous month:
Add the view to your data connection....
![]()
Join syntax for the 2 tables above is:
1 - Create a Global Variable set to [[Current_Year]]-10-25 - This would have to be maintained ie changed EVERY Month
2 - Create an additional view called vwPREVIOUS_CUT_OFF in your database that generates the PREVIOUS_CUT_OFF_DATE. Then JOIN from your FACT table to vwPREVIOUS_CUT_OFF on FACT_Date >= PREVIOUS_CUT_OFF_DATE
If you then create a chart that includes PREVIOUS_CUT_OFF_DATE it will be limited to data up to the cut off, whereas creating a chart that does not include PREVIOUS_CUT_OFF_DATE will show all the data.
SQL SERVER Syntax for creating a view with a PREVIOUS_CUT_OFF_DATE set to 25th of the previous month:
CREATE VIEW [dbo].[vwPreviousCutOff] as
SELECT CONVERT(varchar(12),DATEFROMPARTS(IIF(MONTH(GETDATE()) = 1,YEAR(GETDATE())-1,YEAR(GETDATE())),
IIF(MONTH(GETDATE()) = 1, 12 , MONTH(GETDATE())-1)
,25)) as PrevCutOffDate
Add the view to your data connection....
Join syntax for the 2 tables above is:
[vw_UserActivity].[ActivityDate] >= [vwPreviousCutOff].[PrevCutOffDate]
Nov 29, 2022 - 03:42 PM
In addition to those 2 previous methods, there is a another similar method that does not require additional tables and joins:
3 - Add an additional column to your FACT table in your database that adds one month to the Fact_Date
In My example tables this is
eg - DATEADD(month, 1, dbo.[UserActivity].[ActivityDate]) as ActivityDatePlusOneMonth
Then materialise this new database column in your data connection by creating a new object field...
Use it to Filter in a chart. Rather than use a between filter, simply use 2 filters in combination....
sorry i can't show the full filter value above, it's shown below....
FILTER1: Activity Date less than [[CURRENT_YEAR]]-[[CURRENT_MONTH]]-26
FILTER2: Activity Date Plus 1 Month greater than [[CURRENT_YEAR]]-[[CURRENT_MONTH]]-25
3 - Add an additional column to your FACT table in your database that adds one month to the Fact_Date
In My example tables this is
eg - DATEADD(month, 1, dbo.[UserActivity].[ActivityDate]) as ActivityDatePlusOneMonth
Then materialise this new database column in your data connection by creating a new object field...
Use it to Filter in a chart. Rather than use a between filter, simply use 2 filters in combination....
sorry i can't show the full filter value above, it's shown below....
FILTER1: Activity Date less than [[CURRENT_YEAR]]-[[CURRENT_MONTH]]-26
FILTER2: Activity Date Plus 1 Month greater than [[CURRENT_YEAR]]-[[CURRENT_MONTH]]-25
Add New Comment