1 2 Previous Next 17 Replies Latest reply on Jul 25, 2018 7:44 AM by Haan Haan

# Get Differences btw today and The nearest of any date earlier than today

Hi

Kindly refer to the attached workbook at Sheet 6.

The expected outcome is as such:

Today:[ Amount from Name 1(Cheras and PJ)] / [Total from HQ:A]= 0.2506

Nearest day from Today : The nearest of any date earlier than the search date  for "Today"

Differences : (Today- Nearest day from Today) / Nearest day from Today

(0.2506-1.3)/1.3 = -0.8072

Presume the search date is 1/8/2018.

May I know how to achieve it?

Thanks

• ###### 1. Re: Get Differences btw today and The nearest of any date earlier than today

Hi, Haan

What's the difference of this question comparing to your previous one?

ZZ

• ###### 2. Re: Get Differences btw today and The nearest of any date earlier than today

Hi Haan,

I don't think you can achieve this output if you blend your data sources. Because we can't use LOD{} and Table calculation while blending.

So what I did, I extract your both the sources and joined.

here is my finding

One more doubt, How you're evaluating Nearest day from Today?? I'm getting 13000

To evaluate the correct output I've divided this 13000/10000 so that I can get 1.3

Correct me If I'm doing something wrong.

Workbook attached for your reference. Let us know if this works for you. I've also attached the excel of your sources.

Mahfooj

• ###### 3. Re: Get Differences btw today and The nearest of any date earlier than today

Dear Mahfooj

Thanks for ur help.

The logic of getting 1.3 is

• ###### 4. Re: Get Differences btw today and The nearest of any date earlier than today

Dear ZZ

The difference is the requirement of the calculation is different...

I have added selected measure names in the calculation.( underlined)

Today:[ Amount from Name 1(Cheras and PJ)] / [Total from HQ:A]= 0.2506

Nearest day from Today : The nearest of any date earlier than the search date  for "Today"

Differences : (Today- Nearest day from Today) / Nearest day from Today

(0.2506-1.3)/1.3 = -0.8072

• ###### 5. Re: Get Differences btw today and The nearest of any date earlier than today

Then my assumption was correct. You can go with that approach.

Let us know if you've any other query. Else you can close the thread.

Mahfooj

• ###### 6. Re: Get Differences btw today and The nearest of any date earlier than today

Dear Mahfooj

If the search date is 1/5/2018

Today should be 1.3

(which is the answer of the nearest day from today for 1/8/2018)

So the answer for search date is 1/5/2018 will be

Today= 1.3

Nearest day from today= 6500/1000=6.5

Diff = (1.3-6.5)/6.5=-0.8

Do you get me?

Thanks

• ###### 7. Re: Get Differences btw today and The nearest of any date earlier than today

Hi, Haan

Below is the screenshot of result.

Hope this helps

ZZ

• ###### 8. Re: Get Differences btw today and The nearest of any date earlier than today

Dear ZZ

There is an error.

Referring to the screenshot...Both of the highlighted number should be same isnt?

On 5 Jan nearest day from today should be 4 Jan 's today.

Thanks

• ###### 9. Re: Get Differences btw today and The nearest of any date earlier than today

Hi, Haan

Your nearest date is based on the search date, so when it is 05 Jan 2018, it picks 03 Jan 2018 from PJ and 04 Jan 2018 from Cheras which is 6500+6500.

While it is 04 Jan 2018, it picks up 04 Jan 2018 from PJ and 02 Jan 2018 from Cheras which 6500+2000.

And for search date, when it is 05 Jan 2018, as it exists in both cheras and pj, while when it is 04 Jan 2018, only cheras has matched date, but pj doesn't have a date 04 Jan 2018. that's why you see the difference.

Hope this make sense.

ZZ

• ###### 11. Re: Get Differences btw today and The nearest of any date earlier than today

Dear Ankit

However, the information on 5 Jan is incorrect.

If the search date is 1/5/2018

Today should be 1.3

(which is the answer of the nearest day from today for 1/8/2018)

So the answer for search date is 1/5/2018 will be

Today= 1.3

Nearest day from today= 6500/1000=6.5

Diff = (1.3-6.5)/6.5=-0.8

• ###### 12. Re: Get Differences btw today and The nearest of any date earlier than today

Dear ZZ

If this is the case, how to achieve that?

If the date for Today, nearest date from today, diff is based on demo file.

While if there is no info on the nearest date from today for the branch, will return to 0.

Thanks

• ###### 13. Re: Get Differences btw today and The nearest of any date earlier than today

Hi,

In filters shelf you've kept

Name = Cheras and PJ

HQ = A

Searched Date = 1/5/2018

I don't know what is the logic behind this when you're selecting 1/8/2018 you're dividing Nearest day from today/10000 whereas in case of 1/5/2018 its Nearest day from today/1000. Could you please explain the exact logic for this?

However find attached the latest calculation

Mahfooj

• ###### 14. Re: Get Differences btw today and The nearest of any date earlier than today

Dear Mahfooj

This is the reason

Sorry,sorry....It is 10000 not 1000

1 2 Previous Next