cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Responsive Resident
Responsive Resident

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
Super User II
Super User II

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
Super User II
Super User II

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






Super User II
Super User II

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

Thank you for the assistance, it helped

Super User IV
Super User IV

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

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'

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors