Hello. I have some a table of monthly sales data that I am trying to put into a matrix to display year over year growth per business unit, by year. This matrix is set up with Rows = Business Unit, Columns = Year (filter, top 6), and Values = Measure. (see scrnsht below for what I want it to look like)
I am trying to build a Measure that will dynamically calculate the YoY growth % for a given year, but the row filtering by year seems to be confusing my code.
The following is the current iteration of my code which does yield values, albeit incorrect ones:
Basically I need to tell the measure to look at whatever the year is the of the column that the cell is in, and then take the sum of the revenue for that year divided by the sum of the prior year's revenue. It has to be dynamic so that I will not need to hard code any year numbers.
Silly of me that I didn't notice that before. However, that doesn't work either.
I beleive it has something to do with how the dates work. You see, these numbers come from monthly reports, and are not detailed at the date level. Originally, the data table just had a rolling count of the months (since the year when this report was produced) as a number, and the year of the record, as a number. From these two, I inserted a date column as the first day of the coresponding month and year, formatted as a date. These first-day-of-month dates are unique and stored in the calendar table, and are related to the coresponding month number. Please see the poorly rendered illustration below.
If I try to use the YoY Quick Measure, it complains, "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."
Here is the code from the quick measure. I am going to try playing with it to see if I can adapt it to work with what I have.
YoY% = IF( ISFILTERED('CALENDAR'[DATE]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( SUM('DATA'[REVENUE]), DATEADD('CALENDAR'[DATE].[YEAR], -1, YEAR) ) RETURN DIVIDE( SUM('DATA'[REVENUE]) - __PREV_YEAR, __PREV_YEAR ) )
Hi, folks. I just noticed this post after a long time. So it looks like I did not end up using the above DAX. We used some more hard code because of some special cicumstances we had to factor in. I have learned a lot about Power BI in the mean time, and I would not recommend using either method.
Be very careful about default datetime math functions in PBI, I have found they tend to do illogical things.
For instance, we had a guy with an account which hadn't produced any negative events for something like a year. We originally had coded a rate of negative events measure to use the MTD and YTD functions. In the absence of recent data, these functions actually went back a year and used the last month in which his account recorded a negative event as the starting point for those calculations. We had to fix that by writing our own YTD and MTD calculations.