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

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

       

    • miggy46's avatar
      miggy46
      New Member
      2 years ago

      Hi AlB ,

      Thanks for sharing. 

      I have a similar issue with the script applied not working, I also have no active relationships on the table. 

      Is it possible that the script does not work when e.g. pulling from a query? 

      • AlB's avatar
        AlB
        Community Champion
        2 years ago

        Hi miggy46 

        Right-click on the relationship icon (or line) and choose Properties.  In the window that pops up there will be a checkbox around  the bottom to toggle the relationship active/inactive

         

         

        		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.