Issue Calculating Average YoY Value

[Anonymous]

Hi all,

Having trouble and would appreciate any insight. 

Essentially, I am trying to find a single-value YoY% average for the 10 organizations that comprise my dataset (e.g., org #1's YoY = 3%, org #2's YoY = 5%, average = 4%).

I was able to do this successfully for YTD% by creating a column that calculates current month values compared to previous December ("YTD Enrollment = CALCULATE(([This Month Enrollment]/[Dec 2020 Enrollment])-1). I then used the "average" of that column as a card in the Report view to get a single "YTD %" average across all orgs.

However, when I try to do the same for YoY, I have to substitute values from "this month last year" for Dec 2020 values. The values for "this month last year" are all wrong in the calculated column (but show up CORRECTLY in the report graph).

I've written the "This Month Last Year" measure as: "This Month Last Year = CALCULATE(SUM(Dataset1[Enrollment]), DATEADD(Dataset1[Date], -12, MONTH)" 

The YoY column/measure formula is "YoY Enrollment = CALCULATE(([This Month Enrollment]/[This Month Last Year])-1)"

YoY Enrollment displays correct values when dragged into a graph in the Report view, but when the same code is used to create a calculated column, the values are way off. 

I've tried using different syntax e.g., SAMEPERIODLASTYEAR or DIVIDE instead of CALCULATE, but nothing seems to give the correct values in the calculated column.

Do any of you know what's wrong or how to get the values to display correctly in the table column? Any advice would be immensely appreciated. Thank you!!

[AlexisOlson]

DATEADD and SAMEPERIODLASTYEAR are generally used in measures where you have filter context rather than the row context of a table. The key question is how do you know what "This Month" is in the table context since the row context probably only has a single date?

[Anonymous]

That's a good point, thanks! There is no date column in this particular table, though I played with adding one and it didn't change the output values. The table is tied via relationship view to a "date" column in another table (one-to-many relationship). 

I figured that wasn't the issue becuase the YTD column correctly pulled in "This Month Enrollment" (This Month Enrollment = CALCULATE(SUM(Dataset1[Enrollment]), Dataset1[Date] = MAX(Dataset1[Date]))), which also relies on a date function. 

If DATEADD and SAMEPERIODLASTYEAR specifically are the issues here, is there a function similar to or interacting with MAX that would work in the row/table context?

[AlexisOlson]

It's hard to say what might work without seeing your model.

Generally, you should be using your calendar date dimension table columns rather than fact table date columns in your measures. Generally, these sorts of calculations will only be measures since calculated columns cannot be dynamically responsive to slicers, filters, and cross-filtering in the report.