Year-over-year calculation showing blanks

[DQuigg]

I have a common date table that contains dates through 2020. Some of my fact tables only have dates through 2018. I have a year-over year calculation built by the quick filters that works fine but shows the 2019 year because it is in the common date table with -100% decline as shown below. 

My DAX for this is pretty standard and I understand why it is happening. 

Fall Enrollment (EF_A) YoY% =

VAR __PREV_YEAR = CALCULATE([Fall Enrollment (EF_A)], DATEADD('_Date Periods'[Date], -1, YEAR))

RETURN

    DIVIDE([Fall Enrollment (EF_A)] - __PREV_YEAR, __PREV_YEAR)

I can revise the DAX to the following formula that works but it is extremely slow. I also ideally would like the calculation when used without a date filter context to default to the last year in the enrollment fact table. 

Fall Enrollment (EF_A) YoY% =

VAR Last_Year_in_Fact = MAX('Fall Enrollment (IPEDS EF_A)'[Date])

VAR __PREV_YEAR = CALCULATE([Fall Enrollment (EF_A)], DATEADD('_Date Periods'[Date], -1, YEAR),'_Date Periods'[Date]<= Last_Year_in_Fact)

RETURN

    DIVIDE([Fall Enrollment (EF_A)] - __PREV_YEAR, __PREV_YEAR)

I think I am on the right track but I know there has to be a simpler solution in DAX that performs better.

[amitchandak]

@DQuigg ,

Check if _date_Periods is a date table. With Date table in place you can try datesytd too

Examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DQuigg]

@amitchandak, @parry2k 

Thank you for the response.  Yes, the dates are a date table. The problem is not the calculation - it works fine. The problem is my common date table includes dates into 2020 but not all of my FACT tables (there are several) go to 2020. So I end up with matrix calculations show YoY% of -100% in the last column because the filter context includes 2020 from the date table. I can work around it by filtering out 2020, but I really want my DAX YoY% calculation to restrict to the dates in the FACT table (last year of 2018). I came up with a solution, but it is very slow probably because it iterates over every row in both the FACT and the Date table. I know enough now about DAX to know what the problem is but not how to fix it. If I can set the filter context to max out at the dates included in the FACT table, it should solve the problem. 

[parry2k]

@DQuigg it is not clear if you tried my proposed solution, that is the answer to your question. If that doesn't work (if you tried) then share what is the output and the measure as well.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k]

@DQuigg please test the solution and then we go in the explanation.

[DQuigg]

@parry2k - that worked!  Thank you!

I would appreciate an explanation on why it worked. Is it because it forces the filter context to restrict to the FACT table dates through the second DIVIDE statement.  I need to make sure I understand the DAX code. 

[parry2k · Accepted solution]

@DQuigg you can tweak your original measure like this

Fall Enrollment (EF_A) YoY% =
VAR __PREV_YEAR = CALCULATE([Fall Enrollment (EF_A)], DATEADD('_Date Periods'[Date], -1, YEAR))
RETURN
    DIVIDE([Fall Enrollment (EF_A)] - __PREV_YEAR, __PREV_YEAR) * 
DIVIDE ( [Fall Enrollment (EF_A)], [Fall Enrollment (EF_A)] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[DQuigg]

@parry2k ,

thanks for the solution.  I have to try it out.  To clarify, am I correct then that the last part of the DAX Divide statement is simply to force the filter context back to the original FACT table? The real problem I have is that the filter context includes all dates in the date table and I want the dates to be restricted to what is in the FACT table so I don't get -100% in the last column.