topic Re: Prevent Date Filter From Reducing Less Than A Year in DAX Commands and Tips

Prevent Date Filter From Reducing Less Than A Year

mmahoney045 — Sat, 22 Jun 2024 14:10:49 GMT

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

Re: Prevent Date Filter From Reducing Less Than A Year

lbendlin — Sat, 22 Jun 2024 14:18:23 GMT

think about using REMOVEFILTERS.

You might also want to consider using Visual Calculations instead.

Re: Prevent Date Filter From Reducing Less Than A Year

mmahoney045 — Thu, 27 Jun 2024 16:56:45 GMT

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

)