PREVIOUSYEAR behaving strangely when visual/page is filtered

[s-turn]

Hello,

I'm trying to do something I thought would be simple... calculate the percentage growth in payment volume from year to year.

However, it only seems to work for the last year.  It's blank for earlier years, as you can see in the screenshot here:

You can see what the #Payments1YearBack syntax is at the top of the screenshot.  I've also tried it with DATEADD and with SAMEPERIODLASTYEAR and got identical results.

The #PaymentsTrimmed measure to which it refers is simply 

#PaymentsTrimmed = COUNTA(PaymentsTrimmed[UniquePaymentID])

However... the screenshot above is from a visual that is filtered both at the visual and page level, on a couple of other fields that don't appear in the visual (relating to category of payment).  When I clear all filters, it looks fine (apart from the totals, but I'm not too worried about that as I would be turning them off anyway for the final report).

So, clearly something about the filter context is messing with PREVIOUSYEAR, or so it seems.  Any ideas?  Thanks in advance!

[v-xulin-mstf]

Hi @s-turn,

Could you provide .pbix file after removing sensitive data?

Not sure if the problem comes from the calendar table.

Best Regards,

Link

[s-turn]

I can't easily do that, as the data source is a Power BI dataset that I use for lots of reports.  However, I will try and recreate it and see what happens...

[s-turn · Accepted solution]

Ah - I've just been playing around with the file again, and it now works perfectly!  I've no idea what has changed since the last time I logged in - nobody has changed any of the measures.  How weird.  

[jdbuchanan71]

@s-turn 

I hate when stuff like that happens but glad it is working for you and thank you for posting that your problem was solved.

[jdbuchanan71]

@s-turn 

Two questions (just a stab in the dark).  

1. Is you CalYear column in the visual from your 'Calendar' table?

2. Does your 'Calendar' table contain full years?  Meaning it goes from Jan-1 of the first year to Dec-31 of the last year.  I have seen odd behaviour if this is not the case.

[s-turn]

Yes, CalYear is from my Calendar table, and yes, it contains full years (see screenshot below), though it stops in March 2023 so I suppose the last year is not a full year. 

[s-turn]

Just to give more info on the Calendar table, this is the syntax - I copied it from an article online and added just one calculated column, so I must confess I'm not entirly sure how it does what it does!  I expect it ends on 31/03/2023 because 31/03 is the end of our financial year.

Calendar =

--Inputs--

VAR WeekStartsOn = "Mon"

VAR FiscalStartMonth = 4

--NOTE: Calendar week starts from Monday

--Calculation--

RETURN

ADDCOLUMNS (

CALENDARAUTO ( FiscalStartMonth - 1 ),

"MIndex", MONTH ( [Date] ),

"FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),

"CalMonth", FORMAT ( [Date], "mmm" ),

"CalQtr", "Q"

& CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),

"CalYear", YEAR ( [Date] ),

"Fiscal Week",

VAR FiscalFirstDay =

IF (

MONTH ( [Date] ) < FiscalStartMonth,

DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),

DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )

)

VAR FilteredTableCount =

COUNTROWS (

FILTER (

SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),

FORMAT ( [Dates], "ddd" ) = WeekStartsOn

)

)

VAR WeekNos =

IF (

FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,

FilteredTableCount + 1,

FilteredTableCount

)

RETURN

"Week " & WeekNos,

"Fiscal Qtr", "Q"

& CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,

"Fiscal Year",

VAR CY =

RIGHT ( YEAR ( [Date] ), 2 )

VAR NY =

RIGHT ( YEAR ( [Date] ) + 1, 2 )

VAR PY =

RIGHT ( YEAR ( [Date] ) - 1, 2 )

VAR FinYear =

IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )

RETURN

FinYear,

"CalWeekNo", WEEKNUM ( [Date], 2 ),

"Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),

"Day", FORMAT ( [Date], "ddd" ),

"CustomDate", FORMAT ( [Date], "d/mm" )

)