cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DQuigg
Advocate III
Advocate III

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. 

Dates with 100 percent decline.PNG

 

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
parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

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






Did I answer your question? Mark my post as a solution.

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





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






Did I answer your question? Mark my post as a solution.

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. 

parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





View solution in original post

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors