Fill up gaps with duplicate date

[babyjb1979]

Hi Power Bi Gurus,

I have a table with date and value, I am using the DAX code to fill up the gaps as following:

New Value =

VAR LastNonBlankDate =

    CALCULATE (

        FIRSTNONBLANK ( TableName[Date], 1 ),

        FILTER (

            ALL ( TableName ),

            TableName[Date] >= EARLIER ( TableName[Date] )

            &&TableName[ID] = EARLIER ( TableName[ID] )

                && FIRSTNONBLANK( TableName[Value],1 ) )   )

   RETURN

    CALCULATE (

        MIN( TableName[Value] ),

        FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate )  )

It is perfectly solution. But if there are duplicated date, and value(01/02/2023 which is duplicated) something wrong happened. I have to keep the duplicated rows. 

So I want to introduce a var to summarize the date and value, then I stuck here :

New Value 2 =

VAR tb=SUMMARIZE('TableName',TableName[ID],TableName[Date],TableName[Value])

VAR LastNonBlankDate=

    CALCULATE (

        FIRSTNONBLANK ( [Date], 1 ),

        FILTER (

            ALL ( tb ),

            [Date] >= EARLIER ( [Date] )

            &&[ID] = EARLIER ( [ID] )

                && not( ISBLANK([Value]) )

        ))

RETURN

    CALCULATE (

        SUM( [Value] ),

        FILTER ( ALL ( tb ), [Date] = LastNonBlankDate )

    )

Please help me out!!!

Thank you in advance!

Branko

ID	Date	Value
A	1/1/2023
A	1/2/2023	6
A	1/2/2023	6
A	1/4/2023
A	1/5/2023	12
A	1/6/2023
B	1/3/2023
B	1/6/2023
B	1/7/2023
B	1/8/2023
B	1/9/2023	8
B	1/10/2023
B	1/15/2023
B	1/17/2023	43
B	1/18/2023
C	1/19/2023
C	1/20/2023
C	1/21/2023	11
C	1/22/2023

[babyjb1979]

@Anonymous 
Hi Liu,

The logic appears to be correct, but please check that the data is still incorrect. Please use new data to test

ID	Date	Value
1023201	9/1/2023
1023201	9/2/2023
1023201	9/3/2023
1023201	9/4/2023
1023201	9/5/2023
1023201	9/6/2023
1023201	9/7/2023
1023201	9/8/2023
1023201	9/9/2023
1023201	9/10/2023
1023201	9/11/2023
1023201	9/12/2023	12
1023201	9/26/2023
1023201	9/27/2023
1023201	9/28/2023
1023201	9/29/2023
1023201	9/30/2023
1023201	10/1/2023
1023201	10/2/2023	7
221934	9/1/2023
221934	9/2/2023
221934	9/3/2023
221934	9/4/2023
221934	9/5/2023	5
221934	9/11/2023
221934	9/12/2023
221934	9/13/2023
221934	9/14/2023
221934	9/15/2023
221934	9/16/2023
221934	9/17/2023
221934	9/18/2023
221934	9/19/2023
221934	9/20/2023
221934	9/21/2023
221934	9/22/2023
221934	9/23/2023
221934	9/24/2023
221934	9/25/2023
221934	9/26/2023	16
246075	9/1/2023
246075	9/2/2023
246075	9/3/2023
246075	9/4/2023
246075	9/5/2023
246075	9/6/2023
246075	9/7/2023
246075	9/8/2023
246075	9/9/2023
246075	9/10/2023
246075	9/11/2023
246075	9/12/2023
246075	9/13/2023
246075	9/14/2023	14
246075	9/27/2023
246075	9/28/2023
246075	9/29/2023
246075	9/30/2023
246075	10/1/2023
246075	10/2/2023
246075	10/3/2023	7

[Anonymous · Accepted solution]

Hi  @babyjb1979 ,

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    MAX('Table'[Value]) =BLANK(),
    MINX(
        FILTER(ALL('Table'),
        'Table'[ID]=MAX('Table'[ID])&&'Table'[Date]>MAX('Table'[Date])&&'Table'[Value]<>BLANK()),[Value]),
        MAX('Table'[Value]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[babyjb1979]

@Anonymous 
The logic appears to be correct, but please check that the data is still incorrect. Please use the new data to test. Thank you very much for your time and help!!!

ID	Date	Value
1023201	9/1/2023 0:00
1023201	9/2/2023 0:00
1023201	9/3/2023 0:00
1023201	9/4/2023 0:00
1023201	9/5/2023 0:00
1023201	9/6/2023 0:00
1023201	9/7/2023 0:00
1023201	9/8/2023 0:00
1023201	9/9/2023 0:00
1023201	9/10/2023 0:00
1023201	9/11/2023 0:00
1023201	9/12/2023 0:00	12
1023201	9/26/2023 0:00
1023201	9/27/2023 0:00
1023201	9/28/2023 0:00
1023201	9/29/2023 0:00
1023201	9/30/2023 0:00
1023201	10/1/2023 0:00
1023201	10/2/2023 0:00	7
221934	9/1/2023 0:00
221934	9/2/2023 0:00
221934	9/3/2023 0:00
221934	9/4/2023 0:00
221934	9/5/2023 0:00	5
221934	9/11/2023 0:00
221934	9/12/2023 0:00
221934	9/13/2023 0:00
221934	9/14/2023 0:00
221934	9/15/2023 0:00
221934	9/16/2023 0:00
221934	9/17/2023 0:00
221934	9/18/2023 0:00
221934	9/19/2023 0:00
221934	9/20/2023 0:00
221934	9/21/2023 0:00
221934	9/22/2023 0:00
221934	9/23/2023 0:00
221934	9/24/2023 0:00
221934	9/25/2023 0:00
221934	9/26/2023 0:00	16
246075	9/1/2023 0:00
246075	9/2/2023 0:00
246075	9/3/2023 0:00
246075	9/4/2023 0:00
246075	9/5/2023 0:00
246075	9/6/2023 0:00
246075	9/7/2023 0:00
246075	9/8/2023 0:00
246075	9/9/2023 0:00
246075	9/10/2023 0:00
246075	9/11/2023 0:00
246075	9/12/2023 0:00
246075	9/13/2023 0:00
246075	9/14/2023 0:00	14
246075	9/27/2023 0:00
246075	9/28/2023 0:00
246075	9/29/2023 0:00
246075	9/30/2023 0:00
246075	10/1/2023 0:00
246075	10/2/2023 0:00
246075	10/3/2023 0:00	7

[_elbpower]

Would you like to address gaps in your data by inserting missing dates and updating the value column with the most recent non-blank value? For instance, if there's a missing date like 1/3/2023, would you want to create a new row with that date and a value of 6 copied from the previous date?

[babyjb1979]

I want to fill up the blank with the lastest non-blank value