Show all dates in a selected financial year

Show all dates in a selected financial year

Using a parameter, if we choose Aug-2016 then we need to see all dates from April-2016 to March-2017 and if we choose Nov-2017 then we must see all dates from April-2017 to March-2018.

Solution: Create a parameter of type date which lists all months and years (use add from field option and select the date field) this parameter can be further customized to show only months-year and in ascending/descending orders making it more user friendly.

The parameter should looks like the below image.

Create a calculated field as shown below

This calculation is set to show all dates from April to March using a value of -3. But, you are free to change the number as according to your Financial calendar. Add this calculation to filter shelf and set it to True.

The final result would look something like this.

MTD LTD calculation in Tableau

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