I have a lovely report on sales by year except that I have noticed that any sales in the first week of each year are not picked up in the January totals, nor are they in the December totals, either. I suspect it’s something to do with my Dates table and how the queries are working. I think I may have conflicting queries with my Dates table: I used the eDNA-provided query as my DateQuery, under Other Queries, but changed the source (go into Advanced Editor on the Dates table itself) to dynamically define my dates by Min/Max of dates in my source data. Plus, I added some other values.
Here are my expected values for January Sales:
Jan 2019: David Miley - $371,455. Missing two sales totaling $16,760 recorded on 1/2 and 1/3/2019.
Jay Kleiber - $187,240. Missing $6,800 sale recorded 1/3/19.
Jan 2020: David Miley - $572,280. Missing $24,000 sale recorded 1/3/20
Hi @kjssdca. I took a quick look, and your model and DAX measures are fairly straightforward, so I’d suspect something is wrong with your Dates table. I can’t see the source code for the [Dates] table, so can’t decipher further. Perhaps you could either try to reset your [Dates] table code with the latest from the eDNA source,
or cut-and-paste the source code currently being used for your [Dates] table.
In any event, I saw 2 things to do before going further:
Thanks for posting your sample file and it made troubleshooting easy. Actually, start of week filter is active in your visual and that is why it is not picking up initial days. Otherwise, all your measures are working perfectly fine. You can see below:
Hi @kjssdca, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Thank you for the response. As I explained in my initial post, I did use the eDNA query to create the Dates table, then modified it with another query on the table itself. You can go into Advanced Editor and see source code for both queries.
How do I mark the Dates table [query] as a Data table?
Ah, I knew it had to be something easy - that filter was left over from a previous iteration and I had forgotten all about it. Thank you so much for finding the issue! I’m back on my merry way now. …
Hi @kjssdca. Sorry … I was rushing and got throws by the errors … advanced editor was the key. To mark a table as a “date” table, select it in the Fields pane, then click “Mark as date table” from the Table Tools menu
Your need to have your [Dates] table marked as a date table for the built-in time intelligence in Power BI to work properly. (It’s literally the first thing I check in every forum submission.)
. Cheers!
