Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I want to calculate the variation (%) of the amount beetwen the current Month and the same Month of the previous Year .
Does any one Have a solution ?
Thank You for your support and help
Best regards
You can create a dim_Calendar like these:
TRACE Creating Calendary;
let vMin_Date = '01/01/2022';
let vMax_Date = TODAY();
// Generate a record of each day betwen two dates
[TMP Dim_Calendary]:
LOAD
'$(vMin_Date)' + ROWNO()-1 as DATE_TEMP
AUTOGENERATE vMax_Date-vMin_Date+1;
Dim_Calendary:
NOCONCATENATE LOAD
DATE_TEMP+0 as ORDER_DATE_KEY, // KEY FIELD !!!
DATE(DATE_TEMP) as ORDER_DATE,
-InYearToDate(DATE_TEMP, '$(vMax_Date)', 0) as DATE_IS_IN_ACTUAL_YEAR, // Actual Year
-InYearToDate(DATE_TEMP, '$(vMax_Date)', -1) as DATE_IS_IN_LAST_YEAR // Last Year
RESIDENT [TMP Dim_Calendary];
DROP Table [TMP Dim_Calendary];
Remember that your date field from your fact table needs to have the same name than ORDER_DATE_KEY.
Now on your dashboard you can create a set analysis on your KPI like:
(Sum({DATE_IS_IN_ACTUAL_YEAR = {1}}SALES) - Sum({DATE_IS_IN_LAST_YEAR = {1}}SALES)) / Sum({DATE_IS_IN_LAST_YEAR = {1}}SALES)
In case you want to calculate period over period, I suggest you to learn more about as-of tables.
This post explains all you need to know to create more flexible scenarios.