Comparing between 2 periods (date and time)

[OmarBenSalem]

Hi Everyone,

As you can see, I want to be able to select 2 periods of date and time to compare.

for example, comparing the 2 periods btwn 01/01/2016 to 12/01/2016 and 01/02/2016 to 11/02/2016 while I'am betwn 9h00 and 10h00.

How to do so?

Should I create 2 fact tables, 2 calendar dates and 2 time tables?

then creating 4 variables; vFromDate1, vToDate1 and vFromDate2, vToDate2 ;each 2 triggering one calendar?

Or could that be done with only one calendar with set analysis?

for example, sthing like this: (can you please verify the syntax with me? I couldn't figure out how to do it)

=Sum([Calendar date]={">=date( $(vFromDate) ,'DD/MM/YYYY')&<=date( $(vToDate) ,'DD/MM/YYYY')"}}Sales)

Thank you very much !

[OmarBenSalem]

Hi,

Please have a selection with the field Date Ticket; you'll then see that I have data between 10/01/2016 and 30/01/2016

Thus, I don't understand why have such a result (in 2013) ! and when I try to select dates in 2016 I always have 0 as a result !

[OmarBenSalem]

Now, when I revert to Date_Ticket(the date of the fact table) instead of Calendar_Date(the date of the master calendar I've created with the Date_Ticket like this:

in the fact table : AutoNumber(Date_Ticket, '%Date ID') as [%Date ID]

in the master calendar: AutoNumber([Calendar date], '%Date ID') as [%Date ID] )

I'll have accurate results ! But why can't I have the same results with Calendar Date I've been using in the simple comparisons (WTD MTD and so on) and having accurate results?

Thank you!

[HirisH_V7]

Hi omar,

Make a sample app with some sample data and Mention your Output required.

I hope that way will be easy to get a desired result.

HTH,

Hirish