cancel
Showing results for
Did you mean:

## Year-over-year calculation showing blanks

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.
1 ACCEPTED SOLUTION
Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

7 REPLIES 7
Super User

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.

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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.

Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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.

Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.