Prevent Date Filter From Reducing Less Than A Year

[mmahoney045]

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 customers labeled as "New Tank Distributor" based on current year sales and prior 2 year sales. The measure works perfectly when the dashboard filters on total years, but doesn't return the correct values when filtering on months within a given year. 

How does one allow a calculate function to accept the year of a filter, but ignore any lesser filtering (i.e. months 1,2,3)? I tried parallelperiod instead of addate, but that is still allowing the dashboard filter to reduce the aggregation below a year. Any tips?

Here is the code I believe needs to be revised. 

The below is within an ADDCOLUMN(SUMMARIZE() virtual table: 

"Previous 2 Years All Sales", CALCULATE([Sales ALL Total], PARALLELPERIOD('Fiscal Year Table'[Date],-2,YEAR)) + CALCULATE([Sales ALL Total], PARALLELPERIOD('Fiscal Year Table'[Date],-1,YEAR)),

"Previous 2 Years Tank Sales", CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks", PARALLELPERIOD('Fiscal Year Table'[Date],-2,YEAR)) + CALCULATE([Sales ALL Total],'Product Hierarchy'[Product Category] = "Tanks", PARALLELPERIOD('Fiscal Year Table'[Date],-1,YEAR))

[mmahoney045]

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

    )

)

[lbendlin · Accepted solution]

think about using REMOVEFILTERS.

You might also want to consider using Visual Calculations instead.