cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.