Thanks for the reply from @Ashish_Mathur and @NaveenGandhi , please allow me to provide another insight:
Hi, @PowerRobots99
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
MEASURE =
VAR cc =
MAX ( 'Table'[Dates] )
VAR bb =
CALCULATE (
MAX ( 'Table'[ReportDate] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[PO] = MAX ( 'Table'[PO] )
&& 'Table'[ReportDate] < MAX ( 'Table'[ReportDate] )
)
)
VAR aa =
CALCULATE (
MAX ( 'Table'[Dates] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[PO] = MAX ( 'Table'[PO] )
&& 'Table'[ReportDate] = bb
)
)
RETURN
IF ( ISBLANK ( cc ), aa, cc )
3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This calculated column formula works
Column = COALESCE(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[PO]=EARLIER(Data[PO])&&Data[ReportDate]<EARLIER(Data[ReportDate]))),Data[Date])Hope this helps.
Hi Ashish,
Thank you for the reply,
However, suppose if for specific PO with data like as shown in below image,
In this case, it should give 7/5/2024 in yellow blank cell, however as per our formula it gives maximum of dates. i.e 9/17/2024, is there any way to tweak measure to give previous reporting date's date value
Thanks for the reply from @Ashish_Mathur and @NaveenGandhi , please allow me to provide another insight:
Hi, @PowerRobots99
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
MEASURE =
VAR cc =
MAX ( 'Table'[Dates] )
VAR bb =
CALCULATE (
MAX ( 'Table'[ReportDate] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[PO] = MAX ( 'Table'[PO] )
&& 'Table'[ReportDate] < MAX ( 'Table'[ReportDate] )
)
)
VAR aa =
CALCULATE (
MAX ( 'Table'[Dates] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[PO] = MAX ( 'Table'[PO] )
&& 'Table'[ReportDate] = bb
)
)
RETURN
IF ( ISBLANK ( cc ), aa, cc )
3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply,
Please refer attached screenshot,
Dates should be calculated in place of blanks based on its corresponding PO numbers in previous report dates,
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
