Forum Discussion
Helper IFill Blanks with latest,
Hello Community, please consider helping me out here.
I have this dataset of payments registered in the company, wich is imported monthly from xlsx file.
A | B | C | D | E | F | G |
4 | 1583181 | 0 | 6 | 28/12/2020 | 5/1/2021 | -0,16 |
12 | 1524 | 4 | 7 | 5/1/2021 | 5/1/2021 | 1892,05 |
4 | 1583181 | 0 | 7 |
| 5/1/2021 | 1712 |
1 | 225421 | 0 | 1 | 12/1/2021 | 12/1/2021 | 672,05 |
4 | 1583181 | 0 | 8 | 12/2/2021 | 12/2/2021 | -1711,84 |
1 | 225421 | 0 | 2 | 10/2/2021 | 12/2/2021 | 672,05 |
4 | 1583181 | 0 | 9 | 12/2/2021 | 1711,84 |
For each product (each one is a combination [A]-[B]), payments are scheduled in a series of dues, whose secuential number for each product is [D].
Each Due has a foreseen date for payment [E] and an effective paymen date [F], wich may be earlier or later.
Some clients may introduce changes to the terms of the service wich are registered secuentially for each product in notes [C].
Lastly, [G] is the amount pactually payed.
My issue here is that, as you can see above, some due dates [E] are null in the dataset, and I need to find the way to fill them, for each product (combination [A]-[B]), with the last non-blank date in previous dues [D]. There are nearly 100k records each month, and they are mixed, and usually the date needed for filling is not within the montly batch, so the function "fill down" at PowerQuery is not usefull here.
In the above case, it should be 28/12/2020 in the first null and 12/2/2021 in the second empty cell.
I don´t have too much experience with this and can´t figure it out how to solve this with PowerQuery or DAX syntax.
Hi,
According to your description, I can roughly understand your requirement, you can try this measure:
This is the test data I created based on your description:
First you can go to the Power Query to add an index column to the dataset like this:
Then you can apply and go to the Power BI to create these two calculated columns like this:
Rank_B = RANKX(FILTER(ALL('Table'),'Table'[B]=EARLIER('Table'[B])),'Table'[Index],,ASC,Dense)E_new = var _lastE=CALCULATE(MAX('Table'[E]),FILTER(ALL('Table'),'Table'[Rank_B]=EARLIER('Table'[Rank_B])-1&&'Table'[B]=EARLIER('Table'[B]))) return IF('Table'[E]=BLANK(),_lastE,'Table'[E])And you can create a table chart to place it like this to get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- ValtteriN
Community Champion
Hi,
You can create measure with IF logic. E.g.
var curdate = MAX('Calendar'[Date])
var latestnonblank = CALCULATE(MAX('Table'[Date]),ALL('Calendar'),'Table'[Date]<=curdate) return
IF([your value]=blank(),latestnonblank,[your value])
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
DavidGoldenThanks, but I didn´t work,
The formula should find last non blank date for the secuence of dues within the same product (combination [A]-[B]) and considering only the last modification [C] prior to the empty date.
- v-robertq-msft
Community Support
Hi,
According to your description, I can roughly understand your requirement, you can try this measure:
This is the test data I created based on your description:
First you can go to the Power Query to add an index column to the dataset like this:
Then you can apply and go to the Power BI to create these two calculated columns like this:
Rank_B = RANKX(FILTER(ALL('Table'),'Table'[B]=EARLIER('Table'[B])),'Table'[Index],,ASC,Dense)E_new = var _lastE=CALCULATE(MAX('Table'[E]),FILTER(ALL('Table'),'Table'[Rank_B]=EARLIER('Table'[Rank_B])-1&&'Table'[B]=EARLIER('Table'[B]))) return IF('Table'[E]=BLANK(),_lastE,'Table'[E])And you can create a table chart to place it like this to get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- DavidGolden
Thanks v-robertq-msft , your aproach helped me. I tried your formulas and worked, but returned some incorrect values. I realised the data should be ordered first, within the query. But at trying so, my query and PBIX file broke for some reason I can´t really understand yet, maybe it has something to do with the multiple columns ordering. I guess the issue is solved.
I couldnt say wether it will solve my problem.
