Forum Discussion
Add values at missing dates
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
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
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
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?
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
