Hello,
I'm working on a project for a KPI dashboard and am looking for an optimized solution for developing MoM, YoY and YTD calcs on roughly 20 different KPI measures.
In the end, Each KPI will have something similar to this:
Using that example, I've built out the BIG number using a calc and adding it to the filter shelf using max date. I did this since the most current data month will not always be the current month. Here's that calc:
Question: I'm struggling with figuring out the best way to calculate the previous month's, years (same month) and YTD values and then how to optimally compare the percentage difference to the respective current value. This will be repeated on multiple KPIs. The current value will sometimes be the current month and sometimes be the previous month so can't use today() calcs as a reference. I believe it would be best to have the % difference on a sheet by itself only showing that single result. That way I can duplicate it into a custom shape for the up/down arrows and place on the dashboard as desired.
I've attached the work file in 10.4, but it will eventually need to be reduced to 10.2.1 (fyi). We can use the first tab 'KPI: Median List' as a base example.
Appreciate any best practices and guidance with these calcs.
That solution will not work for me since its using a table calc where I'm not having all the years numbers within one sheet.
