FInd last 2 latest date for each category and count the days if it is more than 80 or 90

[PRABA7385]

Can any one advise how i can write script to count the days if it more than 80 days based on the last 2 dates for each category.

[Akash_Varuna · Accepted solution]

Hi @PRABA7385  , Please try these steps

• Sort data: Sort by Category and Date in descending order.
• Add index: Group by Category , then add an index column starting from 1.
• Filter rows: Keep rows where Index = 1 or 2 (latest two dates).
• Calculate difference: Add a column to calculate the date difference

         DateDifference = Duration.Days([LatestDate] - [SecondLatestDate])

• Add a conditional column:
  • Greater than 90 days → "More than 90".
  • Greater than 80 days → "More than 80".
  • Otherwise → "Within range".
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[V-yubandi-msft]

Hi @PRABA7385 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

Thank You.

[V-yubandi-msft]

Hi @PRABA7385 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thank You.

[V-yubandi-msft]

Hi @PRABA7385 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thank You.

[V-yubandi-msft]

Hi @PRABA7385 ,

Thank you for reaching out to the Microsoft Fabric Community.  @dufoq3 , has provided an accurate solution to your query.  Thank you, @dufoq3 , for your valuable input.

If you need additional information or any modifications to the solution, please let us know.  we're ready  to assist you. Additionally, if possible, please provide sample data to help us address your query more effectively.

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly

[dufoq3 · Accepted solution]

Hi @PRABA7385 , check this, but you should allways provide sample data and expected result!

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLRNTAEIqVYHWQhM10jUzQhc11jA7CQE0TIFEkjQsgCUwhoFppGQyOwWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    GroupedRows = Table.Group(ChangedType, {"Category"}, {{"All", each _, type table}, {"Days", each 
        [ a = Table.MaxN(_, {"Date"}, 2)[Date],
          b = Duration.TotalDays(a{0} - a{1}),
          c = if b > 80 then b else null
        ][c], type table}})
in
    GroupedRows

[Akash_Varuna · Accepted solution]

Hi @PRABA7385  , Please try these steps

• Sort data: Sort by Category and Date in descending order.
• Add index: Group by Category , then add an index column starting from 1.
• Filter rows: Keep rows where Index = 1 or 2 (latest two dates).
• Calculate difference: Add a column to calculate the date difference

         DateDifference = Duration.Days([LatestDate] - [SecondLatestDate])

• Add a conditional column:
  • Greater than 90 days → "More than 90".
  • Greater than 80 days → "More than 80".
  • Otherwise → "Within range".
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance