Forum Discussion
dynamic YoY measures
I need to create a visual that gives the YoY comparison of sales by month. I have no problem with creating the sales measure. However, the years to compare must be dynamic. That is, when there is data for the new fiscal year, the fiscal years to include needs to update accordingly (i.e., gain the new one, but drop the oldest one). (If it matters, because my years are fiscal years, I have to forego all time intelligence and calculation groups functionality.) For example, currently, I have fiscals years 21-22, 20-21, and 19-20. When I have data for the next fiscal year, my YoY comparison must be of 22-23, 21-22, and 20-21, and the visuals must update automatically.
My current solution is to assign a generic "YoY fiscal year" label to each fiscal year and create a measure for each of these. But, the user doesn't like that the labels are "dumb". For example, instead of a measure name like "Sales FY20-21", they see a measure name like "Sales FY 1 Year Ago". And, it's quite tedious to have to create all of these measures (one for each fiscal year). And, if the user ever wants to increase the data range scope, I have to maintain not just the model but also the measures. So, this isn't an ideal solution. Is there a better solution that makes the development not so tedious and which satisfies the user's measure name requirement?
I think I solved my problem. Step 1 is to create generic fiscal year label column in the data model (e.g., FY-0, FY-1, FY-2, etc.). Step 2 is to set the Legend of my line chart to be my regular fiscal year label column (e.g., 21-22, 20-21, 19-20). Step 3 is to place my generic fiscal year label column into the filter pane, and select the default state (e.g., FY-0, FY1, and FY-2, not all fiscal years).
2 Replies
I think I solved my problem. Step 1 is to create generic fiscal year label column in the data model (e.g., FY-0, FY-1, FY-2, etc.). Step 2 is to set the Legend of my line chart to be my regular fiscal year label column (e.g., 21-22, 20-21, 19-20). Step 3 is to place my generic fiscal year label column into the filter pane, and select the default state (e.g., FY-0, FY1, and FY-2, not all fiscal years).