Forum Discussion
Helper IFill up gaps with duplicate date
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:
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 :
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 |
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
5 Replies
- _elbpower
Resolver III
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?
babyjb1979I want to fill up the blank with the lastest non-blank value
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
- 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
