If you have ever stumbled across dynamic KPIs that show year on year change and have no idea how to create them, look no further as this blog will aim to provide a step-by-step on how to create a KPI showing the profit from the previous 12 months of a date selected with a comparison to the year prior to that.
Something that looks like this:
Step 1: Create a Date Parameter, by clicking the drop down by the data pane. The parameter configuration should look as follows
Step 2: Once you have created this right click on it on the data pane and select 'Show Parameter' this will provide this view for you
Step 3: Create your calculations to make the Parameter functional.
Let's break this down by looking at the calculation for the Previous 12 month period first. This calculation is a way to find the total profit for a specific 12-month period that ends on the first day of a month that a user chooses.
The entire expression calculates the sum of profit for orders that meet 2 date conditions.
This calculation first finds the first day of the month using the DATETRUNC() function and the DATEADD('year' ,-1, ..) takes that first of the month date and subtracts one full year. In short, it sets the start date of the period to be exactly 12 months before the first day of the month you're interested in.
Then checks that the date is less than or equal to the beginning of the month selected in the [Date Selector] parameter and makes sure it s the first day of the month using that DATETRUNC().
Provided that both of these conditions are fulfilled then [Profit] is returned.
Now, we can take a look at the Prior 12 Month Period Profit. This calculation is a method to find the total profit for a specific 1 year period that ended 1 year prior to a date that a user chooses.
The entire expression calculated the sum of [Profit] for orders that occurred in a specific 12-month window before the last year.
This calculation is checking that the [order date] is firstly less than the date one year before the date selected by the parameter and the DATEADD('year',-1, [Date Selector] takes the date you choose and subtracts exactly one full year. This sets the exclusive end date of the period and the order date must be before this date. The second condition states that the date should be greater than the date 2 years before the date selected by the date selector parameter. This sets the exclusive start date of the period therefore the order date must be after this date.
Step 4: Add both of these to the text section of the marks card
Step 5: Format within the text pane how you'd like to and you should be left with the following
