Need Help With Prior Year Measure in Power BI Direct Query

[yak8q8]

Hello all,

Please help me figure out what's wrong. I have a visual that needs to show sum of decs by customer and it should show current year and prior year and I should be able to select any year AND any month (multiple months too if needed). My current year measure seems to work very well (first screenshot) but my prior year measure doesn't want to work (second screenshot) and it shows the total sum of decs for each customer. The third screenshot is what i have for the filters. My table is direct query, not import.

[yak8q8]

@Greg_Deckler  Greg, any ideas on how to make the visual load faster? Right now it's more than a minute for the visual to load. Hoping that adjusting the formulas will suffice.

[yak8q8]

@Greg_Deckler thank you so much! i am good to go!

[yak8q8]

@Greg_Deckler now it shows the total for the month for each customer.

[Greg_Deckler · Accepted solution]

@yak8q8 Sounds like you will need to add a filter for your customer then. Grab you customer using MAX or SELECTEDVALUE and then add it to your filter clause.

[yak8q8]

Hi Greg, thank you for your reply. Unfortunately the error I get is "there is not enough memory to complete this operation". Any other ideas?

[Greg_Deckler]

@yak8q8 Try this:

ENTRY VOLUME PY = 
  VAR PRIORYEAR = SELECTEDVALUE('ft_DEC Dates'[Year]) - 1
  VAR CURRMONTHS = DISTINCT('ft_DEC Dates'[Month])
  VAR __TABLE = SUMMARIZE(FILTER(ALL('ft_DEC Dates'),[Year] = PRIORYEAR && [Month] IN CURRMONTHS),[Year], [Month], "__Value", SUM('ft_DEC Dates'[DEC SUM]) )
  VAR __RETURN = SUMX( __TABLE, [__Value])
RETURN
  __RETURN

[Greg_Deckler]

@yak8q8 Couple problems. One, SELECTEDVALUE is not intended for selecting multiple values and you will get problems. Try this:

ENTRY VOLUME PY = 
  VAR PRIORYEAR = SELECTEDVALUE('ft_DEC Dates'[Year]) - 1
  VAR CURRMONTHS = DISTINCT('ft_DEC Dates'[Month]) 
  VAR __RETURN = SUMX(FILTER(ALL('Table'),[Year] = PRIORYEAR && [Month] IN CURRMONTHS),[DEC SUM])
RETURN
  __RETURN