ParallellPeriod and Single Selection Filter

[Gondi63]

I'd like to create a matrix with a Value for [Net Revenue] and [Net Revenue Prior Year].

I've created both measures and they seem to be calculating correctly:

However, if I change the slicer to single select or select only a single year, I lose the Prior Year data:

What's the best method to show both values when only single year is selected?

[Gondi63]

I marked the calendar table as a date table. 

Trying each of these two options, I get the same behavior. Correct calculations when all years are selected, but when I select an individual fiscal year, I get blanks in each of the "prior year" calculations.

[ghaines]

This could happen if your slicer is acting on the year in the fact table, instead of on the calendar.

If not, could you post the formula for "Net Revenue"?

[Gondi63]

Net Revenue = 
    SUMX(
        'Line Items',
        'Line Items'[Net Revenue]
    )

Confirming, the slicers/filters are set on fields from the Fiscal_Calendar DIM table.

[ghaines]

Try changing to SUM('Line Items'[Net Revenue])
I don't think the formula you have there is typical.  I haven't used a formula quite like that.  typically it would be something like:

SUMX(
        VALUES('Line Items'[Product Types]),
        'Line Items'[Net Revenue]
    )

but that is for certain applications, not as a basic calculation.

SUMX calculates the second term for each individual value of the first term.  I'm speaking outside of my experience because I've never used sumx where the first term is an entire table, but I expect that the filter on date is providing a context first, which limits the rows of 'Line Items' that are used in the calculation of the second term.  So "Sum over the currently selected rows of 'Line Items'" has already excluded PY if that makes sense.

[Gondi63]

Thanks for the follow up --

I don't see any behavior change when swiching to SUM()

Net Revenue PY = 
    CALCULATE(
        SUM(
            'Cornerstone Line Items'[Net Revenue]
        ),
        PARALLELPERIOD('Fiscal Calendar'[Fiscal_Year_Date],-1,YEAR
        )
    )

[amitchandak]

@Gondi63 , Make sue the fiscal calendar is marked as a date table.

Also try measure like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))