topic Re: Add values at missing dates in DAX Commands and Tips

Add values at missing dates

peterschuller — Sun, 13 Oct 2019 18:40:59 GMT

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.

Re: Add values at missing dates

AlB — Sun, 13 Oct 2019 22:10:46 GMT

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

Re: Add values at missing dates

peterschuller — Tue, 15 Oct 2019 19:05:51 GMT

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:

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

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.

Re: Add values at missing dates

AlB — Tue, 15 Oct 2019 20:13:14 GMT

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

Re: Add values at missing dates

peterschuller — Wed, 16 Oct 2019 08:12:30 GMT

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.

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?

Re: Add values at missing dates

AlB — Wed, 16 Oct 2019 20:01:48 GMT

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

Re: Add values at missing dates

Anonymous — Tue, 02 May 2023 03:52:24 GMT

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 generated

Date	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

Re: Add values at missing dates

miggy46 — Mon, 11 Sep 2023 09:16:44 GMT

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?

Re: Add values at missing dates

AlB — Mon, 11 Sep 2023 09:16:40 GMT

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.