YOY % DAX Measure

[awfrke]

Hi to all!

I have a single table of values with brands, spends, months in the date format. One brand can have multiple spends during one months.

I have a few functions already intact:

Total Spends = sumx(reports; reports[Spends])

% of Spends =
CALCULATE(
divide(
[Total Spends];
CALCULATE(
SUM(reports[Spends]); all(reports[Brand]))))

(too many "CALCULATE" wraps?)

Then I need to create YOY spends change. Tried various functions, like

Spends PY total =
calculate(
sumx(reports; reports[Spends]);
PREVIOUSYEAR(reports[month_raw]))

And that gets me blanks in the Power BI matrix visualisation.

DATEADD version like this

Spends PY total =
calculate(
sumx(reports; reports[Spends]);
DATEADD(reports[month_raw]; -1; YEAR))

returns this error:

Error Message:

MdxScript(Model) (4, 2)  'reports'[Spends PY total]

And various DIVIDE functions don't work with this to create the needed percentage of change.

So, folks, what should I do?

[BhaveshPatel]

For complete time intelligence patterns, Please visit this blog page at here.

Go to the bottom of the page and you would find the 

YOY % Calculation for your case scenario.

[awfrke]

Okay, I looked at this tutorial and tried to add some new columns and even a new table (entirely with dates and date-related stuff, "Dates" column has no repetitions and is connected to the "reports" table ), that's what came out:

Adopting [PY Sales] formula to my situation wasn't helpful at all -- and I still get blanks.

Spends PY total =
SUMX (
VALUES ( dates[YearMonthNumber] );
IF (
CALCULATE (
COUNTROWS (
VALUES ( dates[Dates] )
)
)
= CALCULATE (
VALUES ( dates[MonthDays] )
);
CALCULATE (
[Total Spends];
ALL ( dates );
FILTER (
ALL ( dates[YearMonthNumber] );
dates[YearMonthNumber]
= EARLIER ( dates[YearMonthNumber] ) - 12
)
);
CALCULATE (
[Total Spends];
ALL (dates);
CALCULATETABLE (
VALUES ( dates[MonthDaysNumber] )
);
FILTER (
ALL ( dates[YearMonthNumber] );
dates[YearMonthNumber]
= EARLIER ( dates[YearMonthNumber] ) - 12
)
)
)
)

What's wrong?

[kcantor]

@awfrke

First make sure your date table has no missing dates. You specified no repeats, but check for missing dates.

Connect the relationship based upon the date column. I am assuming your calendar table is called Calendar and you are connecting on the column named [Dates] for my calculations below.

If your total spends calculation is correct, build on it instead of creating new calculations each time.

Total Spends = sumx(reports: reports[spends])

Last Year Spends = CALCULATE([Total Spends], DATEADD(Calendar[Dates], -1, year))

Growth = [Total Spends]-[Last Year Spends]

% = DIVIDE([Growth], [Last Year Spends])

If you build on existing measures it is easier to find where the breakage occurs. Let me know if this helps.

[awfrke]

DATEADD didn't work for me as I've mentioned in the first message. It returns an error "Function 'DATEADD' only works with contiguous date selections."

Consequentely, nothing beyond that worked.

I have an interim solution of getting several IF() statements wrapped one into another because I need YOY only for three years and the task was quite urgent. 

= CALCULATE(
DIVIDE(
CALCULATE(
[Total Spends];
FILTER(reports;
max(reports[year])
)
);
CALCULATE(
[Total Spends];
reports[year] = 2015
)
)
)-1

This worked, but only for comparing any year to 2015 Total Spends.

Using IF I just did the logic:
IF(max(reports[year])=2016; [(2016/2015)-1]); [another IF of the same logic for another year])

I know it seems like an atrocity to do it this way, but I had to 🙂

Well, back to the case, how to beat the "contiguous date" problem? Tried some solutions from here, didn't work.

Thank you for your reply, by the way.

[v-shex-msft · Accepted solution]

Hi @awfrke,

According to your description, you want to get the result of current year spend / previous year spend, right?

If it is a case, you can refer to below sample:

Source table:

Measures:

Current Year Spends(All) = SUMX(FILTER(ALL(Sheet1),YEAR([Date])=YEAR(MAX([Date]))),[Spends])

Previous Year Spends(all) = SUMX(FILTER(ALL(Sheet1),YEAR([Date])=YEAR(MAX([Date]))-1),[Spends])

Previous Year Spends(Brand) =
var temp= LASTNONBLANK(Sheet1[Brand],[Brand])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[Brand]=temp&&YEAR([Date])=YEAR(MAX([Date]))-1),[Spends])

Current Year Spends(Brand) =
var temp= LASTNONBLANK(Sheet1[Brand],[Brand])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[Brand]=temp&&YEAR([Date])=YEAR(MAX([Date]))),[Spends])

YOY %(Brand) = DIVIDE( [Current Year Spends(Brand)],[Previous Year Spends(Brand)],0)

YOY %(All) = DIVIDE( [Current Year Spends(all)],[Previous Year Spends(all)],0)

Create visual to show the result:

Regards,

Xiaoxin Sheng