Date and time from excel

[delbooth]

I have created a 2 master calendars from the same data source as they refer to different stages of the process ie order entry and collection dates.

My question is that when I use the below expression:

Sum({<ColYear={'2018'},ColWeek = {"$(=Week(Today())-1)"}>}[Quantity Collected])

ColYear and Col]Week refer to the dates in the collection master calendar and gives the correct values.  i then created

Sum({<EntYear={'2018'},EntWeek = {"$(=Week(Today())-1)"}>}[Quantity Collected]) whci refers to the second master calendar.  This does not seem to work and the question  -

Does the date format matter ie for 1st master calendar the date formats are all 27/07/2017 00:00 and the second is 18/04/2017 14:50 in that it has time value as well not 00:00.

Do I need to set the 2nd calendar dates to have time value of 00:00

Thanks

del

[sunny_talwar]

It will depend on your calendar script, but in general it is better to create a master calendar without the time component

[delbooth]

How do I do that I am loading the data using the data load editor, do I set the it to ignore times when creating the master calendar.

Thanks

[sunny_talwar]

May be create a new field just for creating the calendar

LOAD  OrderDate as OrderDateandTime,

    Date(Floor(OrderDate)) as OrderDate

Floor will remove Time and you can now use OrderDate to create your calendar

[Chanty4u]

try this

Date(Trim(floor(Date("OrderDate",'Dateformat'))),'MM/DD/YYYY') as [OrderDate],

[sunny_talwar]

Chanty bhai 2-4 aur expressions functions bhi laga dalo...

[Chanty4u]

haha let him try all 

[sunny_talwar]

But why would you use Trim() after Floor()

[delbooth]

Thanks for all the help

[OmarBenSalem]

If u create a master calendar and its Date field format would be DD/MM/YYYY; don't expect it to join correctly with ur data if the time part exists.

U'll have to eliminate the time part of ur second Date, maybe using floor()