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

denpries

New Member
Joined
Mar 20, 2011
Messages
4
(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)

296x286
/

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
16x16_smiley-tongue.png
and i get frustrated.
Do i miss a certain concept? (very likely)

Hope my questions are clear.

Best regards

DP









 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Add "+0" to the end of your measure, and to the end of the first argument in the DIVIDE in your second question.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

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?

Disable AdBlock

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
Back
Top