Fill empty values in table

[Anonymous]

Hi,

I have a problem with non-listed values in a table. I have a standard timeDimension and a table with orders:

Order Date	Article Number	Stock	Ordered Amount
01.02.2021	12	200	20
02.02.2021	12	180	50
05.02.2021	12	130	10
06.02.2021	12	120	30

As you can see there are no orders on 03.02.2021 and 04.02.2021. What I need is this:

Order Date	Article Number	Stock	Ordered Amount
01.02.2021	12	200	20
02.02.2021	12	180	50
03.02.2021	12	130	0
04.02.2021	12	130	0
05.02.2021	12	130	10
06.02.2021	12	120	30

It is way more complicated than it seems. 
I am open to any solutions, whether with M, Dax, or a new table structure.

Thanks for your help!

[v-xiaoyan-msft]

Hi@Anonymous,

Nice day ! Has your problem been solved? if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards,

Caitlyn Yan

[stevedep]

Suppose you are looking for a running total?

File is attached.

Kind regards, Steve. 

[PaulDBrown · Accepted solution]

@Anonymous 

Here is one way. First the model:

now create the following measures:

1) Simple sums for stock and orders

2) The initial stock for an article:

Starting stock =
VAR StartDate =
    CALCULATE (
        FIRSTDATE ( 'Date Table'[Date] ),
        ALLEXCEPT ( FactTable, FactTable[Article Number] )
    )
RETURN
    CALCULATE (
        [Sum of Stock],
        FILTER ( ALL ( 'Date Table' ), 'Date Table'[Date] = StartDate )
    )

3) the cumulative orders:

Cumulative Orders =
CALCULATE (
    [Sum of order],
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

4) the running stock value:

Running Stock =
CALCULATE (
    [Starting stock] - [Cumulative Orders],
    DATEADD ( 'Date Table'[Date], -1, DAY )
)

all of which are seen in the following table:

and finally delete the unnecessary fields from the visual:

(if you'd rather see 0 instead of blanks for sum of orders, simply add a 0 in the measure):

Sum of order = SUM(FactTable[Ordered Amount]) + 0

I've attached the sample PBIX for your reference

[Anonymous]

Thank you PaulDBrown! This was what I needed! Had to change some little things but all in all thats it!

[BA_Pete]

Hi @Anonymous ,

Create a measure to display your [Ordered Amount] value like this:

_yourAmount = SUM(yourTable[Ordered Amount]) + 0

This will show you zeroes where no other value exists.

If you don't want the zeroes, you can right-click on any of your displayed dimensions and check 'Show items with no data':

 

However, note that this will show ALL values available in the dimensions, so may not be suitable for your use-case.

Pete

[Anonymous]

Hi Pete,
Thanks for your reply, but unfortunately this isn't what I need.  
Selecting "Show items with no data" leaves all values empty except the date.

The measure just adds rows with same date as order date but with zeros.

lh_2020