date table not providing dates till end date

[Anonymous]

Hi Community,

My date table looks like this

00 -DateTable = ADDCOLUMNS(CALENDAR(DATE (2016, 1, 1),TODAY()),"DateAsInteger",FORMAT([Date],"YYYYMMDD"),"SimpleDate", FORMAT([Date],"dd/mm/yyyy"),"Year",YEAR([date]), "MonthNumber", FORMAT([Date],"YYYY/MM"), "YearMonthNumber", FORMAT([Date],"YYYY/MM"), "YearMonthShort", FORMAT([Date],"YYYY/mmm"), "MonthNameShort",FORMAT([Date],"mmm"), "MonthNameLong", FORMAT([Date],"mmmm"), "DayOfWeekNumber", WEEKDAY([Date]), "DayOfWeek", FORMAT([Date],"dddd"), "Quarter", FORMAT([Date],"Q"), "YearQuarter", FORMAT([Date],"YYYY") & "/Q" & FORMAT([Date],"Q"))

Strangley , if I put [Date] in a table, it rightly starts dates from Jan 2016, but the end date,-- which I expect should be 26th of July 2017, if done today, -- is coming as 31st of Dec 2017.

Is this a known thing, or is there a solution to this issue.

Kind regards,

[Anonymous · Accepted solution]

Thanks everyone, I have sort of fixed it with a work around, though my problem is still there, so I am now using the date fromt he actual data table and not from the datetable.

[KHorseman]

I cannot replicate what you're describing. I copy and pasted your formula into a test file and dropped the [Date] column onto a table visual, then sorted in descending order. This is what I got:

[Anonymous]

Thanks @KHorseman. The most bizarre thing is that, It does give me correct output initially, but after some time, when I have started using the query, it starts giving incorrect values. Then I would go to query, re-write TODAY() and press enter, the table gets corrected again. I know sounds ridiculous but not sure what is going on. 😐

[v-huizhn-msft]

Hi @Anonymous,

It's weird, I use your formula to get expected result without any problem. Every time you use the query, please click refresh. 

Best  Regards,
Angelia

[Anonymous · Accepted solution]

Thanks everyone, I have sort of fixed it with a work around, though my problem is still there, so I am now using the date fromt he actual data table and not from the datetable.

[v-huizhn-msft]

Hi @Anonymous,

When you sort data, you will see all the expected data without problem. Congratulations, you have resolved your issue by yourself. Please mark your workaround as answer, so more people will benefit from here.

Best Regards,
Angelia

[Anonymous · Accepted solution]

@KHorseman@v-huizhn-msft

Hi Both,

I was still struggling with the same thing and working with the work around, ie, to put some real data to restrict the dates till date only, and learnt something new. 

It shows dates till end of the year, even if I have put TODAY() function in end date, provided, I have selected 'show hierarchy' in values pane. On the other hand, it shows only dates till the correct day, ie today if I remove hierachy and only view it as 'date'.

Thanks everyone for help.