5 Replies Latest reply on Jul 21, 2019 2:27 PM by Hari Ankem

# Monthly, QTD, YTD totals using a date parameter

I need help with writing out the calculations to sum total sales by month based on a date parameter as well as a QTD and YTD totals based on the date parameter.

The logic would be:

Date parameter example 6/10/2019

Month sales total would only include 6/1 thru 6/10

QTD would include sales from 4/1 thru 6/10

YTD would include sales from 1/1 thru 6/10

Any suggestions would be greatly appreciated.

Thanks

• ###### 1. Re: Monthly, QTD, YTD totals using a date parameter

Here you go: Here are the calculated fields created:

MTD Sales:

IF YEAR([Date])*100+MONTH([Date])=YEAR([Date Parameter])*100+MONTH([Date Parameter]) THEN

[Sales]

END

QTD Sales:

IF YEAR([Date])*10+DATEPART("quarter",[Date])=YEAR([Date Parameter])*10+DATEPART("quarter",[Date Parameter]) THEN

[Sales]

END

YTD Sales:

IF YEAR([Date])=YEAR([Date Parameter]) THEN

[Sales]

END

Hope this helps. A 2019.2 workbook is attached for reference.

• ###### 2. Re: Monthly, QTD, YTD totals using a date parameter

Hello Hari,

This is a really interesting solution! Could you please tell why did multiply year by 100 and 10.

• ###### 3. Re: Monthly, QTD, YTD totals using a date parameter

I am converting the year and month into a format like 201901, 201902, 201903,...201910, 201911 and 201912; and similarly converting the year and quarter into a format like 20191, 20192, 20193 and 20194.

• ###### 4. Re: Monthly, QTD, YTD totals using a date parameter

Hello Hari,

Thank you for the help with this submission.

Is there a way to look at only the data by year using the date parameter?

The logic would be:

My parameter is set for 4/15/2019

My sales number would only be 4/15/2019

My monthly sales number would = 4/1/2019 thru 4/15/2019

My quarterly sales number would = 4/1/2019 thru 4/15/2019

My yearly sales number would = 1/1/2019 thru 4/15/2019

Is this possible?

Thanks

• ###### 5. Re: Monthly, QTD, YTD totals using a date parameter

Guess you need a date filter in this scenario. Updated workbook attached for reference.