YTD, LYTD, MTD and LMTD

[Sumit]

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)

[Taoufiq_Zarra]

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