Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

date table not providing dates till end date

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,

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymousv-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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks Anonymous. 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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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