cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidGolden
Frequent Visitor

Fill 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.

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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:

vrobertqmsft_0-1643699279157.png

 

First you can go to the Power Query to add an index column to the dataset like this:

vrobertqmsft_1-1643699279160.png

 

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:

vrobertqmsft_2-1643699279165.png

 

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.

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
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:

vrobertqmsft_0-1643699279157.png

 

First you can go to the Power Query to add an index column to the dataset like this:

vrobertqmsft_1-1643699279160.png

 

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:

vrobertqmsft_2-1643699279165.png

 

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.

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.

ValtteriN
Super User
Super User

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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks, 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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors