topic Re: Fill up gaps with duplicate date in DAX Commands and Tips

Fill up gaps with duplicate date

babyjb1979 — Fri, 06 Oct 2023 15:08:21 GMT

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

Re: Fill up gaps with duplicate date

_elbpower — Fri, 06 Oct 2023 15:38:36 GMT

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?

Re: Fill up gaps with duplicate date

babyjb1979 — Fri, 06 Oct 2023 17:01:24 GMT

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

Re: Fill up gaps with duplicate date

Anonymous — Tue, 10 Oct 2023 04:17:53 GMT

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

Re: Fill up gaps with duplicate date

babyjb1979 — Tue, 10 Oct 2023 22:14:44 GMT

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

Re: Fill up gaps with duplicate date

babyjb1979 — Tue, 10 Oct 2023 22:23:28 GMT

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