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

[denpries]

(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
- business unit
- 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.

[/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

 

[gazpage]

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

 

[denpries]

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]

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