# Also return dates when sales = 0 in table and charts + cumulative question

#### denpries

##### New Member
(cross post for: https://community.powerbi.com/t5/De...-0-in-table-and-charts-cumulative/td-p/324639)

Dear all,

I have a 'sales' table and a 'dates' table.

The dates table contains :
- Date (from jan2016 until dec 2020)
- month-day
- month-workday
- month
- year
...etc

For simplicity, the sales table contains:
- date
- order number
- net sales amount
Note that there can be many orders per day and these can be all in the same or another business unit.

I have a filter on month and a filter on Business unit.

I made a double direction relation from sales[date] >> datetable[date]. I think this is correct, because i want to be able to show e.g number of orders for a certain month or business unit only and therefore i need the bidirectional filter.

Now i have a table that shows me, when i select a certain business unit and month, the sales per month day.
However, some small business unit could sell only every 5 days. So you would get in the table something like:

Day 1: 500
Day 6: 850
Day 11: 1350
...
etc

However, i would like to see also that the other days are 0. See the example table below, that has no value for month day 8.
(Note that it does show 2x value = 0 , but this is due to rounding as they are in fact not 0)

/

Next to this, i have a question on the running total.
Code:
``[/FONT][/COLOR][COLOR=#333333][RunningSales]] = CALCULATE(DIVIDE(sum(Sales[Sales)]);1000);FILTER(ALLSELECTED(sales[Day in month]);sales[Day in month]<=max(sales[Day in month])))[/COLOR][COLOR=#333333][FONT='inherit']``
The running total works like a charm. But i have three things i dont like.

1. It also does not have values for day 8. I want it to show a value for 8 equal to value for 7
2. I dont use the date table for this at all. I made a calculated column sales[day in month] and used that one, not something from the date table which i am under the impression is the best approach. How would i do this, while still allowing the filters to work?
3. If i do get a table that always fills out values for all days in the month, and i would look at this month, i would like the chart to stop the line after today and not keep a flat line. At he same time, when there is a weekend and therefore no sales, i want the chart to reflect this, and not create a sloped line from the beginning of the weekend to the end of the weekend.

I have been messing around with all this REALLY long now
and i get frustrated.
Do i miss a certain concept? (very likely)

Hope my questions are clear.

Best regards

DP

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### gazpage

##### Active Member
Add "+0" to the end of your measure, and to the end of the first argument in the DIVIDE in your second question.

#### denpries

##### New Member
Add "+0" to the end of your measure, and to the end of the first argument in the DIVIDE in your second question.

Tried. Doesnt work

Adding the 0 makes als JAN FEB MAR appear, all filled with 0.
Also, the cumulative table will show 0 instead of the cumulaive value just before the 0

#### gazpage

##### Active Member
Create a new table with a list of the dates. Needs to be comprehensive and cover every date you want to show. Connect this new table to the dates column in your current table. Put the column from this new table in your visualisation.

Gary

Replies
0
Views
36
Replies
0
Views
143
Replies
10
Views
125
Replies
2
Views
105
Replies
2
Views
125

1,127,582
Messages
5,625,620
Members
416,124
Latest member
DeMoNloK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back