Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a sales table with no rows in the table if the sales don't exist for a particular date. I created a calculated table, but get the same result.
I want the calculated table to insert sales of zero for the missing dates.
Is this even possible? Eventually, I want to have a rolling 12 month column, but my totals are wrong because the months with zero sales are not being included.
hi @Reetz
It is possible by leftjoin a date table with your sales table in Power Query Editor.
Why do you think missing the zero sales rows affecting you calculation? What calculation do you get?
I assume the sales table is not gonna be so small in terms of data pump it with some more data so it has the different products on the same dates. Right now whats causing you a problem most probably is one to one relationship.
Thank you for your response. My actual sales table has a lot more data with a lot of accounts buying many different products on different dates. (Although, my company summarizes those sales dates to be all the first of the month). In actuality, the relationship between the date table and the sales table is a 1 to many. I tried to scale down the example to uncomplicate it.
I went back and added more accounts, products and sales with more dates and got the same results.
What I am looking for is if a specific date has no sales for that account/product, I want to show a row for that missing date/account/product with sales of zero.
User | Count |
---|---|
66 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |