8 Replies Latest reply on Nov 5, 2018 7:24 AM by raoul.tller

# Calculate the lowest value over the last 6 months

Hi,

I have a request from my end-user which includes calculating/selecting the lowest value over the last 6 months. Currently I use the following LoD to return the lowest value on Month of datetime:

MIN({INCLUDE [code],DATETRUNC('minute', [datetime]): SUM([balance])})

Which works. But now we want to use the result of this LoD of the last 6 months: so 6 results, when the end user opens the report in october then you see the lowest value over:

october, september, august, july, june, may

So the result would be -10.132 since thats the lowest result in those 6 months. And this would be the Ref. Level.

When selecting september as month then it would be september, august, july, june, may, april. (month, -6)

The other thing is that the view that they want to see it in only includes the Last TWO months so like this:

Is this at all possible?

• ###### 1. Re: Calculate the lowest value over the last 6 months

Hi Raoul,

Here is What you need and I think You already worked on Last 2 Months Thing and I use FIXED LOD and CONTEXT Filter for Below view you need.

Here is the Calc:

and then

use this Filter

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 2. Re: Calculate the lowest value over the last 6 months

Hi Deepak,

Thanks! But why does code abcd dissappear? There is over 30 codes in my original data set that I would need this MIN_Bal value per code in the same view. So the MIN_Bal of all available codes (only two in the testdata set you can see). So I am looking for both poip -10.132 and abcd

In this data set both of the lowest values are in august, but this will definitely not the the case in my full data set

• ###### 3. Re: Calculate the lowest value over the last 6 months

Instead of today I set the month select (or well date parameter) but I cannot figure out why it only returns one min_balance for just code POIP and not abcd

• ###### 4. Re: Calculate the lowest value over the last 6 months

So I Think you need this:

1 of 1 people found this helpful
• ###### 5. Re: Calculate the lowest value over the last 6 months

Where does this -10,917 number come from, the min balances should be -12.460 and -10.132

• ###### 6. Re: Calculate the lowest value over the last 6 months

I don't know. When I open your file I see those values.So did you get the method to get what you were asking?

• ###### 7. Re: Calculate the lowest value over the last 6 months

Hi Deepak,

If I open your attached workbook I dont see the -10,917 number from you screenshot but I do see this:

The result of the calculation can not be exactly the same for both codes, I would expect it to return -12.460 for abcd and -10.132 for poip, it currently returns -10.132 for both codes?

I want the lowest balance over the last 6 months per code, so all codes will have a different result. So I would expect this false for abcd on augustus 2018 to be True aswell, because for both codes the lowest balance was in august. I think by adding code to calculation 1 it will return the correct result but the Filter still still returns false like before:

Before:

So currently it only returns one lowest value for code POIP since its the lowest value in all data, but I want it to return the lowest per code:

• ###### 8. Re: Calculate the lowest value over the last 6 months

Got the result I wanted by using window_min in the 6 month view and then filtering using FIRST().