Matrix of Previous Year's Sales for Non Contiguous Dates

[PANDAmonium]

Hi All,

I'm trying to make a matrix of previous year's for non contiguous dates. This is going into a larger formula / matrix that uses this to set conditional formatting, but I've broken it down and this is the last piece I need to figure out.

Here's the forumla for my measure to calculate previous year's sales: 

Measure =

VAR PYEAR = MAX('Table'[Date].[Year]) - 1

VAR PVAL = CALCULATE(SUM('Table'[Value]), FILTER(ALLSELECTED('Table'), YEAR('Table'[Date]) = PYEAR))

RETURN PVAL

I included some screenshots on mock data. As you can see, this works when in a table for year only , but now if I want to add customer into the mix it will not reevaluate per customer but only show the sum for the year.

I've also tried 

Parallel Period = CALCULATE(SUM('Table'[Value]),PARALLELPERIOD('Table'[Date],-12,MONTH))

Works fine when evaluated against the total including split by customer, but within the table or matrix each row for year shows either 0 or blank (see second screenshot)

I think my brain is just mush at this point so any help is greatly appreciated. Thanks!

[jdbuchanan71 · Accepted solution]

Hello @PANDAmonium 

I have seen strange behavior when using time intelligent functions (PARALLELPERIOD) without a date table that extends from Jan 1st to Dec 31st.

You can add a basic date table using the following DAX code.

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN
ADDCOLUMNS(
    DateRange,
    "Year",YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "MonthNum", MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm-yyyy"),
    "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ),
    "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) )
)

Then you link the Dates[Date] field into 'Table'[Date].

Next, under modeling, mark the Dates table as a date table.

Then try the measure like this.

PY Value = CALCULATE ( SUM('Table'[Value]), SAMEPERIODLASTYEAR ( DATES[Date] ) )

[PANDAmonium]

Awesome, yeah, that worked. Thank you very much!

It feels so good just to finally blink, lol.

[PANDAmonium]

Actually...I might just need to switch it to a *:1 with a single direction filter. 1 min.

[PANDAmonium]

As soon as I split the table or matrix by customer it throws an error.