Forum Discussion

mmahoney045's avatar
mmahoney045
Regular Visitor
2 years ago
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 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))

 

  • lbendlin's avatar
    lbendlin
    2 years ago

    think about using REMOVEFILTERS.

     

    You might also want to consider using Visual Calculations instead.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    think about using REMOVEFILTERS.

     

    You might also want to consider using Visual Calculations instead.

  • mmahoney045's avatar
    mmahoney045
    Regular Visitor
    2 years ago

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