Aggregate value not filtering for Prior year measure (DATEADD & FY filter)
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.
TOTALMTD and USERELATIONSHIP
Hi All, I am trying to calculate a MTD value based on a date column from my 'Quotes' table that is different to the date column that has an active relationship with my calendar. I think I need to use some sort of combination of TOTALMTD and USERELATIONSHIP? I am calculating a value from my 'Quotes' table. The 'Quotes' table has a 'Created Date' column, which is my active relationship with my calendar table, and a 'Quote Won Date', which is what I want my month-to-date calculation to work from. I do have an inactive relationship established in my model between my calendar table and the 'Quote Won Date' Here is something I have tried which does not work but might illustrate what I am trying to calculate: MTD Value of Quotes Won by Quote Won Date = CALCULATE([Value of Quotes Won],DATESMTD('calendar'[Reporting Date]),USERELATIONSHIP(quoted_opportunities_and_quotes_data[Quote Won Date],'calendar'[Reporting Date])) Really appreciate any help anyone can offer! Many thanks in advance, Chris
