cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peterschuller Frequent Visitor
Frequent Visitor

Add values at missing dates

I have two different tables:

1) a date table

Date

01-01-19
01-02-19
01-03-19
01-04-19
01-05-19
01-06-19
01-07-19
01-08-19
01-09-19

 

2) a table with stockquantities per date, but this only contains a value if the stock has been changed. 

 

Partcode Date            StockQty

A101-01-195
A101-05-196
A101-08-194
A201-03-192
A201-08-193
A201-09-191

 

I would like to write a measure with fills the empty dates in the date table with the value from the previous non blank date in the stock data table. The result should be equal to the table below:

Partcode date StockQty

A101-01-195
A101-02-195
A101-03-195
A101-04-195
A101-05-196
A101-06-196
A101-07-196
A101-08-194
A101-09-194
A201-03-192
A201-04-192
A201-05-192
A201-06-192
A201-07-192
A201-08-193
A201-09-191

 

Can anyone help me? If tried for several hours now and am quit desperate. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Add values at missing dates

@peterschuller 

I had a quick look at the file. There are relationships. If you delete them or make them inactive i believe it works (the resulting table has then around 7 million rows)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

   

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Add values at missing dates

Hi @peterschuller 

Try creating a new table (Table2 is the second one you show):

TableRes =
GENERATE (
    Table2;
    GENERATESERIES (
        CALCULATE ( DISTINCT ( Table2[Date] ) );
        VAR Current_ =
            CALCULATE ( DISTINCT ( Table2[Date] ) )
        VAR Next_ =
            CALCULATE (
                MIN ( Table2[Date] );
                Table2[Date] > Current_;
                ALL ( Table2[StockQty] )
            )
        RETURN
            IF ( NOT ISBLANK ( Next_ ); Next_ - 1; Current_ )
    )
)

You'll have the actual date column in "Value". Delete the column  'Date'  and rename Value as Date.  All this would probably be more elegant in M.

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

  

peterschuller Frequent Visitor
Frequent Visitor

Re: Add values at missing dates

Thanks a lot @AlB,

 

I think this is getting in the right direction, but so far the new table looks almost the same as the original table.

I have selected one PartID and this is how it looked in the original table:

 

Table 1.JPGTable: PartIDStockHistory

In the newly created table the same selection looks like this:

 

Table 2.JPGTable: TableRes

 
I used this DAX formula:
 
Query.JPG
I think I miss the link with my first table (the date table) which contains all the dates of 2018.
The result should be a table with a stockposition for each of the 365 days of 2018.
 
 
 
 

 

Highlighted
Super User
Super User

Re: Add values at missing dates

@peterschuller 

It seems to work in the tests I ran. Have a look at the attached file, where I've also included a "cleaned" version with the updated column names deleting the old "Date" column:   

TableResCleaned = 
SELECTCOLUMNS (
    GENERATE (
        Table1;
        GENERATESERIES (
            CALCULATE ( DISTINCT ( Table1[Date] ) );
            VAR Current_ =
                CALCULATE ( DISTINCT ( Table1[Date] ) )
            VAR Next_ =
                CALCULATE (
                    MIN ( Table1[Date] );
                    Table1[Date] > Current_;
                    ALL ( Table1[StockQty] )
                )
            RETURN
                IF ( NOT ISBLANK ( Next_ ); Next_ - 1; Current_ )
        )
    );
    "Partcode"; [Partcode];
    "Date"; [Value];
    "StockQty"; [StockQty]
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

peterschuller Frequent Visitor
Frequent Visitor

Re: Add values at missing dates

Thanks again for all your help and quick replies @AlB ,

 

I can see that it is working in your pbix. 

Unfortunately it still not working in my file.

My original file is called StockImport and has 505.167 rows.

Table 1.JPGTable StockImport

I created a new table called StockHistory using the following DAX formula:

Query.JPG

The result is a new table Stockhistory which is exactly the same as the StockImport file and contains 505.167 rows.

 

Any idea what goes wrong?

 

 

Super User
Super User

Re: Add values at missing dates

@peterschuller 

I had a quick look at the file. There are relationships. If you delete them or make them inactive i believe it works (the resulting table has then around 7 million rows)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

   

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)