Forum Discussion
YOY % DAX Measure
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
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.
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.
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