Volume Last year not working...

[lennyt99]

Greetings and thanks in advanced...

I have volume categorized by fiscal year and now I want to display YOY and YTD, however it's not working. This is what I have for Volume Last Year:

Volume LY =

CALCULATE(

SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),

DATEADD('Fiscal Calendar'[Daily Date], -12, MONTH

))

and this is what is looks like: 

Please help. 

[Greg_Deckler · Accepted solution]

@lennyt99 Maybe:

Volume LY =
  VAR __Date = MAX('Fiscal Calendar'[Daily Date])
  VAR __LYDate = EOMONTH(__Date,-12)
  VAR __LYEndDate = DATE(YEAR(__LYDate),MONTH(__LYDate),DAY(__Date))
  VAR __LYDateStart = EOMONTH(__LYDate,-12)
  VAR __LYStartDate = DATE(YEAR(__LYDateStart),MONTH(__LYDateStart),DAY(__Date))
RETURN
  SUMX(FILTER('Opportunity Tracker 2.0',[Date]>=__LYStartDate && [Date]<=__LYEndDate),[Annual Volume LBS])

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Greg_Deckler · Accepted solution]

@lennyt99 Maybe:

Volume LY =
  VAR __Date = MAX('Fiscal Calendar'[Daily Date])
  VAR __LYDate = EOMONTH(__Date,-12)
  VAR __LYEndDate = DATE(YEAR(__LYDate),MONTH(__LYDate),DAY(__Date))
  VAR __LYDateStart = EOMONTH(__LYDate,-12)
  VAR __LYStartDate = DATE(YEAR(__LYDateStart),MONTH(__LYDateStart),DAY(__Date))
RETURN
  SUMX(FILTER('Opportunity Tracker 2.0',[Date]>=__LYStartDate && [Date]<=__LYEndDate),[Annual Volume LBS])

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[lennyt99 · Accepted solution]

@Greg_Deckler Scratch that last post...

So it's displaying the correct abmount of volume last year, however no values are appearing?? This is odd

[lennyt99]

@Greg_Deckler Your amazing!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Greg_Deckler]

@lennyt99 Sorry, forgot the ALL:

Volume LY =
  VAR __Date = MAX('Fiscal Calendar'[Daily Date])
  VAR __LYDate = EOMONTH(__Date,-12)
  VAR __LYEndDate = DATE(YEAR(__LYDate),MONTH(__LYDate),DAY(__Date))
  VAR __LYDateStart = EOMONTH(__LYDate,-12)
  VAR __LYStartDate = DATE(YEAR(__LYDateStart),MONTH(__LYDateStart),DAY(__Date))
RETURN
  SUMX(FILTER(ALL('Opportunity Tracker 2.0'),[Date]>=__LYStartDate && [Date]<=__LYEndDate),[Annual Volume LBS])

[lennyt99]

@Greg_Deckler I like your thinking!

Unfortunately it didn't work. I have a relationship created with my fiscal calendar to the "date" in the opportunity tracker. It's so werid I don't know why it's not working. I would assume sameperiodlast year would work... It's not even returning anything

Thank you in advnce