Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please help me with Expressions for Showing YTD, LYTD, MTD and LMTD (with help of variables )of Sales based on "Order Date" considering the present date is 29-Dec-2012 (Considering Jan to Dec financial year)
Hi,
Assume that we will use the expression to calculate sales.
and that you have modeled the date dimension with each line is represented by a PeriodID, Year, Quarter, Month, Period.
then the Times variables will be as follows :
YTD (Year-To-Date) : Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},Year = {$(=Max(Year))},Quarter = ,Month = ,Period = >} Sales)
QTD (Quarter-To-Date) Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},QuarterID = {$(=Max(QuarterID))},Year = ,Quarter = ,Month = ,Period = >} Sales)
MTD (Month-To-Date) Sum({$<PeriodID = {$(=Max(PeriodID))},Year = ,Quarter = ,Month = ,Period = >} Sales)
Current Year-To-Date versus the same period last year
(Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, Year ={"$(=Max(Year))"}, Quarter = , Month = , Period = > } [Sales)
/
Sum({$<PeriodID = {"<=$(=Max(PeriodID) - 12)"}, Year ={"$(=Max(Year) - 1)"}, Quarter = , Month = , Period = > } Sales))
-1
the same for Current month versus same month last year or Current month versus previous month, ...
NB:
PeriodID: 201901,201902,201903....
Year:2019
Month:Jan,Feb,Mar,...
Quarter:Q1,Q2,Q3;Q4