3 Replies Latest reply on Sep 25, 2018 8:23 AM by Mahfooj Khan

# Get last amount for YTD, MTD, QTD

Hi

I have a parameter and calculation field as such which identify the YTD, MTD, QTD

[Period] = 'YTD' and year([Date]) = year('TODAY')

OR

([Period] = 'QTD' and year([Date]) = year('TODAY'))

and DATEPART('quarter', [Date]) = DATEPART('quarter', ('TODAY'))

OR

([Period] = 'MTD' and year([Date]) = year('TODAY')

Now, problem that i m facing is creating formula for:

If the selection isOutcome should beFormula
YTDthe amount for the last day of the year (prior to 'TODAY')?
QTDthe amount for the last day of the quarter (prior to 'TODAY')?
MTDthe amount for the last day of the month (prior to 'TODAY')?

Thank you.

• ###### 1. Re: Get last amount for YTD, MTD, QTD

Hi,

Find my approach below,

Create a string parameter like this

Then create a calculated field using parameter. Modify the logic with your fields.

Once done then drag the fields and show the parameter

Workbook attached for your reference. Let us know if this help.

Mahfooj

• ###### 2. Re: Get last amount for YTD, MTD, QTD

Thanks Mahfooj.

May I know the logic behind of the formula?

IF DATEDIFF([Select Period],[Order Date],TODAY())=0 THEN [Sales] END

• ###### 3. Re: Get last amount for YTD, MTD, QTD

Logic is quite simple,

We're taking datediff() between date from your data source and TODAY() based on parameter.

DATEDIFF('parameter value',[Date],TODAY())

parameter value

MTD=month //default value is month whereas MTD is display value

QTD=quarter

YTD=year

Hope this help.

1 of 1 people found this helpful