cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Year appearing in Calendar but Absent in Database

I have a data source PharmaAuditGW which is in my SQL Server.  I have quiried the database to search for the existence of data between certain dates.  I checked for data for the year 2015 as i was having strange "Year" values in my Year slicer in Power BI.  The SQL query is as follows:

PharmaGWSQLQuery.PNGThe query above shows there is no records with year 2015 in my data source.  I construct my calendar as follows:

 

GWAuditCalendar.PNGHowever, when i put a "Year" slicer in my report, I get the following:

 

CalendarYear.PNG

I am puzzled why the year 2015 appears in my Calendar when it does not exist in my data source.

 

Please assist in troubleshooting.

 

Best regards,

Chris 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Year appearing in Calendar but Absent in Database

hi @Chitemerere - can you query for a distinct list of years available in your SQL DB; or what result do you get when you get when you query for MIN of "Date Dispensed"

The Calendar function in Power BI will create dates within the range provided - so if your MIN date is in 2012 and MAX is 2020 then it will generate dates from 01/01/2012 to 12/31/2020 - including 2015 even if you do not have data for the same

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks! 

View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Year appearing in Calendar but Absent in Database

@Chitemerere , I can see different dates used in SQL and Power BI for Min date for Calendar. Make sure you check Database on same date.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Patron
Post Patron

Re: Year appearing in Calendar but Absent in Database

Many thanks for your response.  It just the same column 'Date Dispensed' which is a datetime and tranformed into a date as new column as 'Dates'

Highlighted
Memorable Member
Memorable Member

Re: Year appearing in Calendar but Absent in Database

hi @Chitemerere - can you query for a distinct list of years available in your SQL DB; or what result do you get when you get when you query for MIN of "Date Dispensed"

The Calendar function in Power BI will create dates within the range provided - so if your MIN date is in 2012 and MAX is 2020 then it will generate dates from 01/01/2012 to 12/31/2020 - including 2015 even if you do not have data for the same

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks! 

View solution in original post

Highlighted
Super User II
Super User II

Re: Year appearing in Calendar but Absent in Database

@Chitemerere 

Are you using import mode for the data source? If so, can you select the fact table in table view and sort by date ascending and see what the first date is?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Highlighted
Post Patron
Post Patron

Re: Year appearing in Calendar but Absent in Database

Thank you for the assistance, it helped

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors