Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The query above shows there is no records with year 2015 in my data source. I construct my calendar as follows:
However, when i put a "Year" slicer in my report, I get the following:
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
Solved! Go to Solution.
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!
Proud to be a Super User!
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?
Proud to be a Super User!
Paul on Linkedin.
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!
Proud to be a Super User!
Thank you for the assistance, it helped
@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.
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'
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |