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!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.
Join our Community Sticker Challenge 2025
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Fabric Community Update - February 2025
Find out what's new and trending in the Fabric community.
