Calculations for MTD, LMTD, YTD and LYTD
Creation of MTD, LTD, LMTD, LYTD calculations involves two
steps
Firstly,
Setting the starting month of the financial year, the below
calculation helps the tableau to force the calendar year to consider from the
offset month mentioned in the calculation. The same thing can be achieved using
date option set to the FY start month in tableau and skip the setting FY
process.
Setting FY to start from April
Create a parameter choose date
Create a calculated field to offset the date to April. Date
of accounting is the available date in DB. And
Choose a date is a date filed created using parameter
Calculated field name = FY-Date
of Accounting
//This will move the date to 3 months ahead to 3months from
Jan to (Jan to April)
DATEADD ('month', 3, [Date Accounting])
Create a parameter Choose Date with
date type and Create a calculated field name= FY
Choose Date
//This will set the parameter to
sync with the FY calculation
DATEADD ('month', 3, [Choose Date])
Create MTD calculation field
Month to date
(MTD)
IF DATEDIFF ('year',
[FY-Date of Accounting], [FY Choose Date]) =0
AND
MONTH ([FY-Date
of Accounting]) =MONTH ([FY Choose Date])
THEN
[AMOUNTS]
END
Last month to date
MTD
IF DATEDIFF ('year', [FY-Date of
Accounting], [FY Choose Date]) =1
AND
MONTH ([FY-Date of Accounting]) =
(MONTH ([FY Choose Date]) -1)
THEN [AMOUNTS]
END
Last year to date LYTD
IF
DATEDIFF ('year', [FY-Date of
Accounting], [FY Choose Date]) =1
AND
MONTH ([FY-Date of Accounting]) <=MONTH
([FY Choose Date])
THEN
[AMOUNTS]
END
Last year Month to date
LMTD
IF
DATEDIFF ('year’, [FY-Date of
Accounting], [FY Choose Date]) =1
AND
MONTH ([FY-Date of Accounting])
<=MONTH ([FY Choose Date])
THEN
[AMOUNTS]
END
