topic Calculated Table that shows rows with no data in DAX Commands and Tips

Calculated Table that shows rows with no data

Reetz — Tue, 21 Feb 2023 20:41:39 GMT

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.

Re: Calculated Table that shows rows with no data

olgad — Tue, 21 Feb 2023 21:32:17 GMT

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.

Re: Calculated Table that shows rows with no data

Reetz — Tue, 21 Feb 2023 22:12:06 GMT

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.

Re: Calculated Table that shows rows with no data

FreemanZ — Tue, 21 Feb 2023 23:18:34 GMT

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?