Aggregate value not filtering for Prior year measure (DATEADD & FY filter)

[Anonymous]

Good Afternoon All,

I have 3 relevant tables: FactActualProfits, FactAOP, and DimTime. Both Fact tables are only related to DimTime.
I have two slicers that are set to single select: Fiscal Year and Benchmark comparison

I'm creating a dashboard for a finance department that wants to be able to view different fiscal years and select between benchmarking those years against the the annual operating plan for that year or the prior fiscal year. My problem is when the current fiscal year is selected, the prior year values show for the entire year. I'd like them to only show up until the most recent TimeID for the current fiscal year. I'm able to exlude the data on a monthly/quarterly basis but the measure still aggregates to the yearly total. 

Excel Representation with current single slice selections. If you were to place the below measures in a matrix and apply the same filters, it would look like this:

		Actual Net Sales	Prior Year Net Sales	PY or AOP Net Sales
FY 2022
	Oct-22	$                 68,000	$                        74,000	$                        74,000
	Nov-22	$                 59,000	$                        70,000	$                        70,000
	Dec-22	$                 62,000	$                        46,000	$                        46,000
	Jan-23	$                 49,000	$                        52,000	$                        52,000
	Feb-23		$                        49,000
	Mar-23		$                        71,000
	Apr-23		$                        58,000
	May-23		$                        54,000
	Jun-23		$                        67,000
	Jul-23		$                        59,000
	Aug-23		$                        58,000
	Sep-23		$                        73,000
Total		$              238,000	$                      731,000	$                      731,000
				^^^^^^^^^^^^^^^^^
				I need this to say $242,000

Relevant DAX formulas:

Actual Gross Sales = SUM(FactProfit[CALC_GROSS_SALES])

Actual Sales Costs = SUM(FactProfit[CALC_SalesCost])

Actual Net Sales = [Actual Gross Sales] - [Actual Sales Cost]

Prior Yr Net Sales = CALCULATE([Actual Net Sales], DATEADD(DimTime[day_date], -1, YEAR))

AOP Gross Sales = SUM(FactAOP[CALC_GROSS_SALES])

AOP Sales Cost = SUM(FactAOP[CALC_SalesCost])

AOP Net Sales = [AOP Gross Sales] - [AOP Sales Cost]

PY or AOP Net Sales = 
     var CFY = IF(

                              MONTH(TODAY()) < 10

                              , YEAR(TODAY())

                              , YEAR(TODAY()) + 1

                         )

     var MaxDateID = MAX(FactProfit[TimeID])

     return

     SWITCH(TRUE()

          , SELECTEDVALUE(Benchmark[Benchmark]) = "vs Prior Year"

               , SWITCH(TRUE()

                    , SELECTEDVALUE(FY[fiscal_year]) = CFY

                         , CALCULATE([PY Net Sales], FactProfit[TimeID] <= MaxDateID)

                         , [PY Net Sales]

          , SELECTEDVALUE(Benchmark[Benchmark]) = "vs AOP"

               , SWITCH(TRUE()

                    , SELECTEDVALUE(FY[fiscal_year]) = CFY

                         , CALCULATE([AOP Net Sales], FactAOP[TimeID] <= MaxDateID)

                         , [AOP Net Sales]

          ,  BLANK()

     )

This works when AOP is selected which I'm assuming is because it's a seperate table and doesn't need a time intelligence function but when comparing for Prior Year I'm getting the above issue.

[amitchandak]

@Anonymous , Try a measure like

if(isblank([Actual Net Sales]), [Prior Year Net Sales],[Actual Net Sales])

[Anonymous]

@amitchandak 

I tried your measure but that leads to showing Current Year Totals for first 4 months (only months we have data for), Prior Year totals for last 8 months, and the aggregate total is for the first 4 months being shown (Actual Net Sales). 

I also tried:

IF(ISBLANK([Actual Net Sales]), blank(), [PY Net Sales])

but this returns the same result that I was previously getting where the first 4 months are filled in (because thats what we have current year data on), the rest of the months are blank, and the aggregate is still showing the entire Prior Year Total (all 12 months of data). My issue is the aggregate value not abiding by the cutoff date or in the above case, excluding time periods being ignored due to blank values for Actual Net Sales.