Also return dates when sales = 0 in table and charts

[denpries]

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.

[RunningSales]] = CALCULATE(DIVIDE(sum(Sales[Sales)]);1000);FILTER(ALLSELECTED(sales[Day in month]);sales[Day in month]<=max(sales[Day in month*])))

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

[denpries]

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.

[RunningSales]] = CALCULATE(DIVIDE(sum(Sales[Sales)]);1000);FILTER(ALLSELECTED(sales[Day in month]);sales[Day in month]<=max(sales[Day in month*])))

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

[v-huizhn-msft]

Hi @denpries,

For the first question, @Greg_Deckler have answered it correctly.

For second question, I am still confusing, please share more detailed information for further analysis.

For third issue, we can't break line where the value is 0. Please review this feature request and vote it. You can also create an idea in this website.

Best Regards,
Angelia

[Greg_Deckler]

In the Values area for your visualization, you should be able to click on the drop down for you numeric field and choose "Show items with no data", that may fix your issue. By default 0's or blanks are not displayed in tables and matrices. If that doesn't work, often you can do a CONCATENATE with "" to turn 0's to text. Also, in a table or matrix, you can include a column or measure that always has a value and then those rows will show up.

[denpries]

Unfortunately, its not working this way in my case.

I would like to show this using a few images but i dont see a way to upload them.

This is what i do now:

Sales in k = DIVIDE(sumx(FILTER(TABLE;TABLE[Workday in month]<[someworkday]);TABLE[SALES]);1000)

This measure of course results into a value. Dont be distracted by the filter though: it just selects a subsection of the full table.

Row headers:

Day in month* = if(TABLE[currentdate].[Date]>TODAY();day(today());TABLE[currentdate].[Day])

Column header is the month label

This gives me a matrix with sales per month-day (vertical) and month label (horizontal) but only when the workday is before a certain set someworkday. If i have a slicer and select only 3 months, i will see OKT NOV DEC.

If there are no items at all with sales at 15th of OKT, this cell is BLANK.

Doing e.g. +0 to the whole sales formula works, but then also all non-sliced months show up in the table, since of course, the measure result is blank due to the filter context, but you force it to 0 with the +0.

Messing around with "" is - in my opinion - a bit off. Its a measure, we dont want to force it into a text measure, right? Plus it has the same result as adding +0.

So what i think about what should happen (but not quite too much into into the dax yet...) is that you want to only add a +0 for outcomes in the filtered context. Not to everything...

[v-huizhn-msft]

Hi @denpries,

First, you can load the images by click the "Photos" button.



Second, I can still confusing about your requirement and problem, could you please share your .pbix file for further analysis?

Best Regards,
Angelia

[denpries]

Hope this brings some clarity. I cannot copy the PBX as it contains classified information unfortunately.

I could create a phony document, but hope the stuff below clears up manners.

Left table:

 Workday in month = calculated column. Every row represents a sales entry with date, this sets the workday

Workday in month = LOOKUPVALUE(Date2Workday[MonthWorkday];Date2Workday[Datum];if(ZANALYSIS_PATTERN[currentdate].[Date]>TODAY();today();ZANALYSIS_PATTERN[currentdate].[Date]))

This gives me back only those rows in the table where workday < current workday.

Net Sales** in k = DIVIDE(sumx(FILTER(ZANALYSIS_PATTERN;ZANALYSIS_PATTERN[Workday in month]<[currentworkday]);ZANALYSIS_PATTERN[Net Sales (excl. pd)]);1000)

With fiscal month on the legend, the left matrix is made.

Similar set up for right, cumulative table:

[Running net sales (excl. pd) [wkdy]]] = if(max(ZANALYSIS_PATTERN[Workday in month])>=[currentworkday];BLANK();1000*CALCULATE([Net Sales** in k];FILTER(ALLSELECTED(ZANALYSIS_PATTERN[Workday in month]);ZANALYSIS_PATTERN[Workday in month]<=max(ZANALYSIS_PATTERN[Workday in month]))))

And it works! Butt.... a lot of blanks. I want to return a 0, but ONLY if it is within filter scope! 

If i would do the following for example:

Net Sales** in k = DIVIDE(sumx(FILTER(ZANALYSIS_PATTERN;ZANALYSIS_PATTERN[Workday in month]<[currentworkday]);ZANALYSIS_PATTERN[Net Sales (excl. pd)])+0;1000)

(the +0 solution). Note, the +0 at the end gives similar results

I get almost what i want, except all 0 for non filtered months as well ( i have a filter on OKT NOV DEC)

[v-huizhn-msft · Accepted solution]

Hi @denpries,

In the following visualization, you only want to display OCT NOV DEC month value's, and don't show part in black box, right? If it is, add [Month] column in visual level filter, let [Month] is not equal to Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep. Check if it works fine.

Best Regards,
Angelia

[denpries]

That works, of course.

But, its not a good solution for my problem. The reason for this is that i use a slicer to select the months to be seen. 

Fiddling in visual filters is not really what i want to achieve.

Maybe its just not possible yet. Im thinking of solutions where you want to replace BLANK but ONLY when in filter context. Hmm

[v-huizhn-msft]

Hi @denpries，

Aside from the solution I poested, I can't figure out other better way to do it. I will post update if there is new idea.

Thanks,
Angelia