How to Get Prior Year Annual Total from Entire Dataset

[IsaacAsher]

Been struggling for ages on this one, and almost there, thanks the help from the community.  

I have a DAX formula to determine the average recovery rate from the prior year.  The problem is that when I select an example date range of 1/1/2015 through 12/31/2019, the resulting dataset starts on 1/1/2016, presumably because the formula column is NULL... since there is nothing in the dataset for the year prior to the dates selected. 

I'm looking for some assistance on how to tweak this formula so it will still give me the data from the year prior to the selected range.  So in our example, the formula should be looking to our average recovery rate in 2014, despite the filter being chosen for 2015-2019.  

Here is the formula (which is working perfectly from year 2 forward): 

PY Ann Rec Rate = 
VAR _CurrentDate = MAX(Batches[Received])
VAR _Year = YEAR(_CurrentDate)
VAR _AnnualTotal = 
CALCULATE(
    DIVIDE(SUM(Batches[Gross Collected]), SUM(Batches[Net Dollars Placed]), 0),
    YEAR(Batches[Received]) = _Year - 1
)

RETURN
_AnnualTotal

I"m positive it needs something adjusted with the YEAR(Batches[Received]) = _Year - 1, but not sure at all what it should be.  

Any help would be much appreciated. 

[amitchandak]

@IsaacAsher , move your/date to seprate table. In case od date a date table.

and try a formula like

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

in case date you can use

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[Greg_Deckler · Accepted solution]

@IsaacAsher Why not something like:

Measure =
  VAR __CurrentDate = MIN(Batches[Received]) //current minimum date in context
  VAR __Start = DATE(YEAR(__CurrentDate)-1,1,1)
  VAR __End = DATE(YEAR(__CurrentDate)-1,12,31)
  VAR __Table = FILTER(ALL('Batches'),[Received]>=__Start && [Received]<=__End)
RETURN
  DIVIDE(SUMX(__Table,[Gross Collected]),SUMX(__Table,[Net Dollars Placed]),0)

[IsaacAsher]

@Greg_Deckler  Wow thanks so much!!

I think this is much closer, but not quite there.  

This function:

DATE(YEAR(__CurrentDate),1,1)

is evaluating to 01/01/1900 12:00:00 AM, and likewise the one for __End is evaluating to 12/31/1900 12:00:00 AM.  I was surprised because the logic looks like exactly what I need, and I'm not sure what this result means or how to react.  

Any thoughts? 

[Greg_Deckler]

@IsaacAsher - Well, what that is telling me is that the year is evaluating to 1 which seems odd. Which that would mean that 

MIN(Batches[Received])

Is for some reason returning something very odd. Is that really a date column and does it have a full date with year and such? You might try debuggin with something like this:

Measure =
  VAR __CurrentDate = MIN(Batches[Received]) //current minimum date in context
  VAR __Start = DATE(YEAR(__CurrentDate)-1,1,1)
  VAR __End = DATE(YEAR(__CurrentDate)-1,12,31)
  VAR __Table = FILTER(ALL('Batches'),[Received]>=__Start && [Received]<=__End)
RETURN
  __CurrentDate & "" //DIVIDE(SUMX(__Table,[Gross Collected]),SUMX(__Table,[Net Dollars Placed]),0)

[IsaacAsher]

@Greg_Deckler 

Batches[Received] is a datetime field but does not have the full hierarchy because there are gaps in the dates (no weekends, no holidays, etc).  

My date table is called MyCal, but when I use MyCal[Date] in place of Batches[Received], I get one total across all dates for all values for all of history.... and hence only one value in the result column.  

[Greg_Deckler]

@IsaacAsher - So are you saying there are blanks? If so that's like the problem, you are getting a blank as the min. So maybe:

VAR __CurrentDate = MINX(FILTER('Table',NOT(ISBLANK([Received])),[Received])

or

VAR __CurrentDate = MINX(FILTER('Table',[Received]<>"",[Received])

[IsaacAsher]

@Greg_Deckler 

When i tried both of those solutions for the __CurrentDate, they both gave the same error for the MINX: "Parameter is not the correct type"

[Greg_Deckler]

@IsaacAsher Sorry, missed some parens:

VAR __CurrentDate = MINX(FILTER('Table',NOT(ISBLANK([Received]))),[Received])

or

VAR __CurrentDate = MINX(FILTER('Table',[Received]<>""),[Received])