how to always have latest quarter sales in a matrix ( remoing external filters)

[Nimazn]

Hi,

I have a matrix below, including customers and their KPIs, I need to keep the latest quarter sales by disabling the user's external filters like Year and Month. In other words, it is desired that the user's filter works on "TotalSales (PCS)" & "YOY%Change" and don't work on others.

I removed external filters on "Current Q" and "last 30 days" but it is almost impossible to remove that effect on the "Last Quarter."

I have tried the ALL() function, but it seems it doesn't work on the "PREVIOUSQUARTER()" function.

filters are as below,

all formulas are as below:

TotalSales(PCS) = SUMX(fsales,RELATED(dItemMaster[QTY_PER_PACK])*fSales[Shipped_QTY])

YOY%Change =

VAR LastYear=

CALCULATE([TotalSales(PCS)],SAMEPERIODLASTYEAR(dDate[Date]),dDate[VaildDateforYOY])

VAR ThisYear=[TotalSales(PCS)]

Return

IF(HASONEVALUE(dDate[Year]),DIVIDE(ThisYear-LastYear,LastYear)*100)

Current Q = CALCULATE(TOTALYTD([TotalSales(PCS)],DATESBETWEEN(dDate[Date],TODAY(),ENDOFQUARTER(dDate[Date]))),ALL(dDate[Year]))

Last 30 days = CALCULATE([TotalSales(PCS)],(DATESINPERIOD(dDate[Date],TODAY(),-30,DAY)))

** The troubled one **:

last Quarter = CALCULATE([TotalSales(PCS)],PREVIOUSQUARTER(dDate[Date]))

Summary:  I want to see the "last Quarter" as a solid value and untouchable by any external filter. 

Please note, that I don't want to create a separate matrix to solve it through edit interaction. All KPIs should be together. 

Thanks in advance.

[DataInsights · Accepted solution]

@Nimazn,

A different approach is to add the calculated column below to your date table:

Relative Quarter = 
VAR vToday =
    TODAY ()
VAR vResult =
    DATEDIFF ( vToday, dDate[Date], QUARTER )
RETURN
    vResult

Then rewrite your measure as follows:

last Quarter =
CALCULATE ( [TotalSales(PCS)], dDate[Relative Quarter] = -1 )

[Nimazn]

Thank you very much!

It seems that there is no way to remove the effect of external filters on the Time Intelligence built-in functions like "previous quarter" without creating a calculated column.

 To remove the effect of external filters (year and month), I added the All() function to your formula: 

LastQuarter =

CALCULATE ( [TotalSales(PCS)], dDate[Relative Quarter] = -1,ALL(dDate[Month]),ALL(dDate[Year] ))