Running total and null values

[LS1700]

Hello,

I have some different tables with sales and I am building a new table with a coulmn where I entered the days of the month (1-31).

I managed to add measures to this table with the units sold for each day and the MTD (running total). 

for the MTD I uesd the formula :

MTD Invoiced = CALCULATE(SUM(Invoicing[Qty]),FILTER(ALLSELECTED(Invoicing),Invoicing[Day]<=MAX(Invoicing[Day])))

 this formulas works great! it also work with different filters.

The problem is that, if in the sales column there is no value, then in the MTD reports nothing.

How can I tell him, "if you find nothing, then bring the value from the previous row"? is that possible?

Below you can see what it is shown when rappresented in a table. 

Day 0, 1, 5 have zero sales. and the MTD is empty.

thanks

LS

 

[Sean]

Couple things you can try

1) Go to the Date filed in the Values area of the Table Visual => click the down arrow => select Show Items with No Data

2) Reference the Date column from your Calendar Table instead

MTD Invoiced =
CALCULATE (
    SUM ( Invoicing[Qty] ),
    FILTER (
        ALLSELECTED ( CalendarTable ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)
    + 0

The + 0 is optional but it will give you basically leading zeros if your Calendar Starts prior to any transactions and you want those dates to show 0.

For example first few days of a Year selected in a Slicer!

Hope this helps!

[LS1700]

Hey,

thanks for your answer.

Show items with no data is already selected.

I cannot reference to the Date because I intentionally want to look at the day of the month. I want to compare the results with other months or other data (such as open orders...).

I appended to the invoice table 31 lines, so that I have all days of the month. Now it is a bit better, but as soon as I filter out (E.g. I select a specific area) I have null values.

The "+0" did not work either. it creates 0 values.

Any other suggestion?

thanks

LS

[LS1700 · Accepted solution]

I found the solution!

Wrong: MTD Invoiced = CALCULATE(SUM(Invoicing[Qty]),FILTER(ALLSELECTED(Invoicing),Invoicing[Day]<=MAX(Invoicing[Day])))

Correct: MTD Invoiced = CALCULATE([Invoiced Qty],filter(ALLSELECTED('Table for Chart'),'Table for Chart'[Day Index]<=max('Table for Chart'[Day Index])))

As you can see, the second is referencedto the new table I created. now it show each time the MTD.