Forum Discussion

peterschuller's avatar
peterschuller
Frequent Visitor
6 years ago
Solved

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...
  • AlB's avatar
    AlB
    6 years ago

    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

       

AlB's avatar
AlB
Community Champion
6 years ago

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's avatar
peterschuller
Frequent Visitor
6 years ago

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: PartIDStockHistory

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

 

Table: TableRes

 
I used this DAX formula:
 
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.
 
 
 
 

 

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      peterschuller
      Frequent Visitor
      6 years ago

      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 StockImport

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

      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?

       

       

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        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