5 Replies Latest reply on Sep 25, 2018 11:35 PM by Haan Haan

# Formula for difference of min and max date of a period

Hi all

For example the period is "Month".

What is the formula to get: Value for (max date of the month) minus Value for (min date of the month)

Same goes to " Quarter" and " Year"

Value for (max date of the quarter) minus Value for (min date of the quarter)

Value for (max date of the year) minus Value for (min date of the year)

Thank you.

• ###### 1. Re: Formula for difference of min and max date of a period

HI Haan,

Yo can try below approach:

1. Create a parameter:

2. Create below variables:

Date =

CASE [Select Period]

WHEN "Year" then YEAR([Order Date])

WHEN "Month" then MONTH([Order Date])

WHEN "Quarter" then DATEPART('quarter', [Order Date])

END

Min Value = {FIXED [Date]: MIN(DAY([Order Date]))}

Max Value = {FIXED [Date]: MAX(DAY([Order Date]))}

Diff = [Max Value]-[Min Value]

1 of 1 people found this helpful
• ###### 2. Re: Formula for difference of min and max date of a period

Hi,

Is that what you wanted?

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

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Formula for difference of min and max date of a period

HI Haan,

Not quite sure what do you mean by min and max, but if you are asking the day in that period, the formula should be.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Formula for difference of min and max date of a period

Thanks everyone.

Actually i m referring to get the amount (eg Sales) on the max and min date

• ###### 5. Re: Formula for difference of min and max date of a period

Thanks Mahfooj.

Does your approach return the sales amount on the max/min date, without aggregating?

As what i got is, the value is too large, and the min and max date is the same when i select YTD.