11 Replies Latest reply on Mar 1, 2019 6:30 AM by Yuriy Fal

# Date Diff from 2 Data Sources

Hi All,

We need to calculate the difference between 2 dates which are coming from 2 excel sheets - Forecast Date and Actual Date.

Tried and went through other questions posted but still not able to figure out:

Tried the calc:

DATEDIFF('day',MAX([Forecast Date1]),(ATTR([Drawdown].[Actual Drawdown Date])))

however this does not show the value and appear blanks.

Have performed data blending on these variables in the data.

Request help on this please.

Message was edited by: Shivi Bhatia Please use this latest file.

• ###### 1. Re: Date Diff from 2 Data Sources

Hi Shivi,

Could you please come up with a sample workbook?

Choosing from possible solutions would depend on

how the actual data are structured and related.

May be joining the tables on proper columns

(instead of making a data blend) would work.

Yours,

Yuri

• ###### 2. Re: Date Diff from 2 Data Sources

Hi Yuriy,

Attached an excel file.

Please consider Foretasted Amount from Forecast 25012019 tab and actual drawn money from Drawn 31012019 tab.

For ease of calculation we can select a week's data - 25th Jan to 31st Jan. This should have 96 cases in Drawn sheet and 95 in Foretasted.

• ###### 3. Re: Date Diff from 2 Data Sources

Hi Shivi,

Please find the attached workbook.

The [YF : Date Diff] is a Row-Level Calculation,

so it can be either a Dimension or a Measure --

depends on the question(s) to the data.

So the question remains what you could do with that info?

I've mentioned the 'Date' selection (from 2019-01-25 till 2019-01-30),

but the actual date field has not been defined. Which one should be used?

Yours,

Yuri

• ###### 4. Re: Date Diff from 2 Data Sources

Hi Yuriy,

Possibly you may have missed to add the workbook. Could 'you please share the workbook.

Regards, Shivi

• ###### 5. Re: Date Diff from 2 Data Sources

Hi Shivi,

It's there attached in my reply.

Please open the thread in the browser

and look for the clip icon in the lower left of my reply.

Yours,

Yuri

• ###### 6. Re: Date Diff from 2 Data Sources

Hi Yuriy,

Thanks for the workbook.

I see that you have done a full outer join on customer name, however this is the mocked data i had shared and it may not be necessary that customers which appear on Forecast tab are also on Drawn tab and vice-versa.

Hence i did data blend.

I am uploading the excel file again, with some proper customer names, hope it helps now.

We need to calculate who are those customer and their respective amounts who were forecasted but did not drew and who draw but were not forecasted with their amount.

Thank you, Shivi

• ###### 7. Re: Date Diff from 2 Data Sources

Hi Shivi,

you wrote:

We need to calculate who are those customer and their respective amounts

who were forecasted but did not drew and who draw but were not forecasted with their amount.

Those customers would have the [YF : Date Diff] value of Null

Yours,

Yuri

• ###### 8. Re: Date Diff from 2 Data Sources

Hi Yuriy,

Unfortunately this is not the case.

For instance, there are 10 customers who were forecasted but only 8 of them drew the amount. Similarly from these 8 there could be 1-2 customers who were actually not forecasted but drew the money.

Hence there will always be customers and always be some associated amount.

I am adding a new excel file - Calculate Variance for reference please. Please let me know in case there is more information needed.

• ###### 9. Re: Date Diff from 2 Data Sources

Hi Shivi,

Please find the attached.

The point with [YF : Date] calculation is --

it depends on which of the two dates

one could take as a baseline.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 10. Re: Date Diff from 2 Data Sources

Thank you so much. This is what i was looking for.

you have been assisting very patiently. Thanks for all your support.

Regards, Shivi

• ###### 11. Re: Date Diff from 2 Data Sources

Shivi, you're welcome.