topic Re: ParallellPeriod and Single Selection Filter in DAX Commands and Tips

ParallellPeriod and Single Selection Filter

Gondi63 — Mon, 23 Jan 2023 21:04:02 GMT

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?

Re: ParallellPeriod and Single Selection Filter

amitchandak — Tue, 24 Jan 2023 01:45:25 GMT

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]))

Re: ParallellPeriod and Single Selection Filter

ghaines — Tue, 24 Jan 2023 03:37:54 GMT

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"?

Re: ParallellPeriod and Single Selection Filter

Gondi63 — Tue, 24 Jan 2023 18:43:59 GMT

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

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

Re: ParallellPeriod and Single Selection Filter

Gondi63 — Tue, 24 Jan 2023 18:57:39 GMT

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.

Re: ParallellPeriod and Single Selection Filter

ghaines — Tue, 24 Jan 2023 21:20:08 GMT

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.

Re: ParallellPeriod and Single Selection Filter

Gondi63 — Tue, 24 Jan 2023 21:27:51 GMT

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 ) )