Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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,

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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.

View solution in original post

Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
KHorseman
Community Champion
Community Champion

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:

 

CalendarToday.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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
Not applicable

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.

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.