Date filling tricky challenge

[PowerRobots99]

Hi everyone,

I am having a table like this, 

Now, for the first blank in Dates column (7/14/24 - 1212 - Blank) row, we have to get date value from previous week i.e 7/5/2024

and 

for the second blank in Dates column (7/14/24 - 3454 - Blank) row, we have to get date value from previous week i.e 7/10/2024,

Currently, with this formula, we are getting maximum date values for that specific PO, how can we tweak this formula to get required results as mentioned above..

 Column = COALESCE(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[PO]=EARLIER(Data[PO])&&Data[ReportDate]<EARLIER(Data[ReportDate]))),Data[Date]) 

ReportDate	PO	Dates
5/14/2024	1212	7/24/2024
5/14/2024	3454	6/21/2024
5/20/2024	1212	7/23/2024
5/20/2024	3454	6/21/2024
5/20/2024	1212	7/23/2024
5/20/2024	3454	6/21/2024
5/26/2024	1212	9/17/2024
5/26/2024	3454	6/21/2024
6/2/2024	1212	8/17/2024
6/2/2024	3454	6/21/2024
6/9/2024	1212	8/17/2024
6/9/2024	3454	7/5/2024
6/16/2024	1212	7/24/2024
6/16/2024	3454	7/5/2024
6/23/2024	1212	8/17/2024
6/23/2024	3454	7/5/2024
7/7/2024	1212	7/5/2024
7/7/2024	3454	7/10/2024
7/14/2024	1212
7/14/2024	3454

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(ISBLANK(Data[Dates]),LOOKUPVALUE(Data[Dates],Data[ReportDate],CALCULATE(MAX(Data[ReportDate]),FILTER(Data,Data[PO]=EARLIER(Data[PO])&&Data[ReportDate]<EARLIER(Data[ReportDate]))),Data[PO],Data[PO]),Data[Dates])

Hope this helps.

[lkalawski]

@PowerRobots99 ,

Please find the .pbix file here: PBI 

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[lkalawski · Accepted solution]

Hi @PowerRobots99 ,

Below is the code that returns you the value from the previous known week for the selected PO.
This is a calculated column that fills in values ​​that are empty.

Correct Date = 
VAR CurrentPO = [PO]
VAR CurrentReportDate = [ReportDate]
VAR PreviousReportDate =
    CALCULATE(
        MAX(comm[ReportDate]),
        FILTER(
            comm,
            comm[PO] = CurrentPO &&
            comm[ReportDate] < CurrentReportDate
        )
    )
VAR LastDateInPreviousReportDate =
    CALCULATE(
        MAX(comm[Dates]),
        FILTER(
            comm,
            comm[PO] = CurrentPO &&
            comm[ReportDate] = PreviousReportDate &&
            NOT(ISBLANK(comm[Dates]))
        )
    )
RETURN
    IF(
        ISBLANK([Dates]),
        LastDateInPreviousReportDate,
        [Dates]
    )

 Result:

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[PowerRobots99]

Perfect !!!

Thank you so much !!!

[PowerRobots99]

Thank you for the reply,

Could you please share the .pbix file, many thanks...