Forum Discussion
Solved
Prevent Date Filter From Reducing Less Than A Year
Hello, I intentional built a dashboard that does not force a specific time period comparison so that previous years or months could be investigated. I created a measure that counts the number of ...
think about using REMOVEFILTERS.
You might also want to consider using Visual Calculations instead.
this kind of got me there, but I later realized the real issue was a different measure I created that wasn't working properly within the context of a fiscal year calendar.
The ultimate solution looked something like:
ParallelPeriodFiscalYear =
VAR CurrentDate = MAX('Fiscal Year Table'[Date])
VAR FiscalYearStartMonth = 4
-- Determine the start of the current fiscal year
VAR StartOfCurrentFiscalYear =
IF(
MONTH(CurrentDate) >= FiscalYearStartMonth,
DATE(YEAR(CurrentDate), FiscalYearStartMonth, 1),
DATE(YEAR(CurrentDate) - 1, FiscalYearStartMonth, 1)
)
-- Determine the end of the current fiscal year
VAR EndOfCurrentFiscalYear =
EOMONTH(StartOfCurrentFiscalYear, 11)
-- Calculate the equivalent period in the previous fiscal year
VAR StartOfPreviousFiscalYear =
DATE(YEAR(StartOfCurrentFiscalYear) - 1, MONTH(StartOfCurrentFiscalYear), DAY(StartOfCurrentFiscalYear))
VAR EndOfPreviousFiscalYear =
EOMONTH(StartOfPreviousFiscalYear, 11)
-- Calculate the measure for the previous fiscal year
RETURN
CALCULATE(
[Sales ALL Total], -- Replace [YourMeasure] with the measure you want to compare against
FILTER(
ALL('Fiscal Year Table'),
'Fiscal Year Table'[Date] >= StartOfPreviousFiscalYear && 'Fiscal Year Table'[Date] <= EndOfPreviousFiscalYear
)
)