Why not use DISTINCT to create a calendar table?

[Anonymous]

If I use

CALENDAR (
FIRSTDATE(table[date]);
LASTDATE(table[date])),

I get all dates between the first and last. That is OK.

But if I use

DISTINCT(table[date]),

I get all dates that really exists in my table. If there is no sale happening on weekends, these dates will be missing, I know, but I think it is also OK.

But why is DISTINCT not recomended?

Why everyone recomends to use CALENDAR instead?
Please, I am looking for examples on possible problems that might occur using one or another function...

Maybe problems using SAMEPERIODLASTYEAR or PARALLELPERIOD? I don´t know.

[v-huizhn-msft]

Hi @Anonymous,

>>DISTINCT(table[date]) I get all dates that really exists in my table.

DISTINCT Function returns a one-column table that contains the distinct values from the specified column. If the date column have no multiple value, it will return all dates.

>>If there is no sale happening on weekends, these dates will be missing, I know, but I think it is also OK.

If you want to miss the dates, you can add filter. You can create a new table display your expected result.

Table 4 = CALCULATETABLE(Table1,FILTER(Table1,Table1[Column]<>BLANK()))

>>But why is DISTINCT not recomended? Why everyone recomends to use CALENDAR instead? Please, I am looking for examples on possible problems that might occur using one or another function. Maybe problems using SAMEPERIODLASTYEAR or PARALLELPERIOD? I don´t know.

Could you please post your sample data and list expected result, so we can analysis which function should be used.

Best Regards,
Angelia

[Anonymous]

hum...what I meant is:
why should my main base calendar table have all contiguous dates from start to end if my raw data does not have all contigous dates?
i mean, it is ok that my data does not include weekend dates, but why must i filter full dates in the main calendar table?
sorry, i dont have an example now, i just want to understand the correct concept. list of DOs and DONTs for calendar().
if my calendar table does not include weekends dates, but neither my raw data, so i belive this is fine. will it be fine? why not?

[KHorseman · Accepted solution]

@Anonymousa date table for time intelligence functions must have all contiguous dates with nothing skipped. Not even weekends. Their entire functionality relies on it. Most of them will simply return an error if you feed them a non-contiguous date column.

Think of a case where you used something like SAMEPERIODLASTYEAR() or DATEADD() to compare this year's sales to last year's sales. In your scenario the two years had completely different sets of dates that the function now has to attempt to compare. Say you're looking at sales for the 1st quarter of this year. If we're ignoring weekends then that's January 2, 2017 - March 31, 2017 since January 1 was a Sunday. When it goes back to look at the same dates last year it will fail. It will not be able to find the same range since January 2 and 3 were Saturday and Sunday, and since we skipped January 1, 2017, you'll miss January 1, 2016 even though it should be considered part of quarter 1. And in the middle you'll miss all of those weekdays last year that were weekend days this year.

[Anonymous]

second part:

why must i use MIN/MAX instead of FIRSTDATE/LASTDATE?

why is this wrong? I´ve read a lot of other posts and blogs and still couldnt find out.

CALENDAR (
   FIRSTDATE(table[date]);
   LASTDATE(table[date])),

why is this the correct way?
CALENDAR (
   MIN(table[date]);
   MAX(table[date])),

[KHorseman]

For the same reason PARALLELPERIOD() and the others would fail. All time intelligence functions work by reading through a contiguous column of dates. They're not really doing math on those dates, rather they are reading them like a calendar and  doing comparisons between those dates according to their filter context. FIRSTDATE() and LASTDATE() included. Those functions at a fundamental level expect a contiguous column of dates as their input, and they fail if they receive anything else.

In the case of FIRSTDATE() and LASTDATE() it isn't so much a failure because they can't find some dates to compare. It's just because you're giving them the wrong type of data in the first place. It's the same kind of failure as you would get if you tried to feed a text column to a math function. Even if you have a column full of nothing but numeric characters, if the column's data type is text, math functions will return an error if you try to use it. Same thing here.

MIN() and MAX() on the other hand are math functions, and dates are fundamentally just a special type of number. Math functions don't care about contiguous dates. They don't work by comparing lists like time intelligence functions do; they just do the math.

[Anonymous]

great! once again, thanks a lot!!

[Anonymous]

great! now I got the idea. thanks a lot!