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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors