Forum Discussion
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
| A1 | 01-01-19 | 5 |
| A1 | 01-05-19 | 6 |
| A1 | 01-08-19 | 4 |
| A2 | 01-03-19 | 2 |
| A2 | 01-08-19 | 3 |
| A2 | 01-09-19 | 1 |
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
| A1 | 01-01-19 | 5 |
| A1 | 01-02-19 | 5 |
| A1 | 01-03-19 | 5 |
| A1 | 01-04-19 | 5 |
| A1 | 01-05-19 | 6 |
| A1 | 01-06-19 | 6 |
| A1 | 01-07-19 | 6 |
| A1 | 01-08-19 | 4 |
| A1 | 01-09-19 | 4 |
| A2 | 01-03-19 | 2 |
| A2 | 01-04-19 | 2 |
| A2 | 01-05-19 | 2 |
| A2 | 01-06-19 | 2 |
| A2 | 01-07-19 | 2 |
| A2 | 01-08-19 | 3 |
| A2 | 01-09-19 | 1 |
Can anyone help me? If tried for several hours now and am quit desperate.
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
8 Replies
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
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.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
AlB
For personal enrichment, could you clarify how this piece of code is working? In the sample file that you share the above code works as intended, but when I tried to incorporate it into my own PBI, sometimes it's not generating a date
e.g. in the table below - for unclear reasons - 2023-04-26 was not generatedDate Present in original table Generated by your code 2023-04-18 True True 2023-04-19 True True 2023-04-20 False True 2023-04-21 False True 2023-04-22 True True 2023-04-23 True True 2023-04-24 True True 2023-04-25 True True 2023-04-26 False False 2023-04-27 True True 2023-04-28 True True 2023-04-29 True True
