Forum Discussion

blueredyellow's avatar
blueredyellow
Frequent Visitor
5 months ago
Solved

Dates Since last report

I have picked up a new Project management reporting tool that is based on PowerApps that then feeds into semantic models that I then report on in PowerBi, I am self-taught in PowerBi and PowerQuery, but making good progress.

 

One of my tables is project status reports.  Which is essentially where the PMs create project status reports.

I believe the critical fields here are :-

  • Project Name
  • Report Date - simply the date report.
  • Latest Report - this is just a flag so I can filter the latest reports
  • Days since report date - I created this in Power Query so I can filter the latest report and see the days from today since the last report.

So every time a project status report is created a new record is created.

There are multiple projects in the same table of data.

 

I would like to create is an another column that finds the last report for that project and calculates the dates since the last record?  So for each record I have days since last record for that project.

 

Is this possible?  I can then create trend charts to show improvements in the frequency of the PMs creating reports.

Hope that makes sense.....

Need Help
  • MarkLaf's avatar
    MarkLaf
    5 months ago

    Since we're in the PQ forum, here is a solution with M. It looks a little crazy but should be relatively performant.

    let
    Source = sample,
    NewColType = [Dates since last report=[Type=Int64.Type,Optional=false]],
    Sort = Table.Sort(Source,{{"Project Name", Order.Ascending}, {"Report Date", Order.Ascending}}),
    Group = Table.Group(
        Sort, {"Project Name"}, {{
            "rows", each [
                base = Table.Buffer( _ ), 
                new_rows = List.Generate( 
                    ()=>0,each _<Table.RowCount(base),each _+1, 
                    each let current_row = base{_} in 
                    current_row & Record.FromList(
                        if _ = 0 then {null}
                            else let last_row = base{_-1} in
                            {Duration.Days( current_row[Report Date] - last_row[Report Date] )}, 
                        { Record.FieldNames( NewColType ){0} } 
                    ) 
                ) 
            ] [ new_rows], 
            type list
        }}, 
        GroupKind.Local 
    ),
    ToTable = Table.FromRecords( 
        List.Combine( Group[rows] ), 
        type table Type.ForRecord( Type.RecordFields( Type.TableRow( Value.Type(Source) ) ) & NewColType, false)
    )
in
    ToTable

     

    Tested with the below sample (generated with gemini):

    Sample (excerpt)

    Project Name Report Date Latest Report Days since report date
    Project A 2/24/2026 Yes 18
    Project A 2/18/2026 No 24
    Project A 1/9/2026 No 64
    Project A 1/2/2026 No 71
    Project A 10/9/2025 No 156
    Project A 9/30/2025 No 165
    Project B 12/15/2025 Yes 89
    Project B 12/12/2025 No 92
    Project C 1/12/2026 Yes 61
    Project C 12/4/2025 No 100
    Project C 10/12/2025 No 153
    Project C 10/2/2025 No 163
    Project C 9/15/2025 No 180
    ... ... ... ...
    Project BT 1/11/2026 Yes 62
    Project BT 12/6/2025 No 98
    Project BU 1/8/2026 Yes 65
    Project BU 1/6/2026 No 67
    Project BV 2/20/2026 Yes 22
    Project BV 1/3/2026 No 70
    Project BV 1/1/2026 No 72
    Project BV 12/23/2025 No 81

     

    Output

     

6 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    You can do it in DAX if you prefer:

    Previous Report Date =
VAR CurrentProject = 'Reports'[Project Name]
VAR CurrentDate = 'Reports'[Report Date]
RETURN
    CALCULATE (
        MAX ( 'Reports'[Report Date] ),
        FILTER (
            'Reports',
            'Reports'[Project Name] = CurrentProject
                && 'Reports'[Report Date] < CurrentDate
        )
    )

Days Since Previous Report =
DATEDIFF ( 'Reports'[Previous Report Date], 'Reports'[Report Date], DAY )

     

    • lbendlin's avatar
      lbendlin
      Super User
      5 months ago

      You could also use window functions or TOPN to find the "penultimate" record.

    • blueredyellow's avatar
      blueredyellow
      Frequent Visitor
      5 months ago

      Thank you for the response. I have never used DAX, but I am willing to have a go.

      Looking at the script you have written with my basic programming skills, it makes sense!

      I'll give it a go and report back!

       

  • MarkLaf's avatar
    MarkLaf
    Super User
    5 months ago

    Since we're in the PQ forum, here is a solution with M. It looks a little crazy but should be relatively performant.

    let
    Source = sample,
    NewColType = [Dates since last report=[Type=Int64.Type,Optional=false]],
    Sort = Table.Sort(Source,{{"Project Name", Order.Ascending}, {"Report Date", Order.Ascending}}),
    Group = Table.Group(
        Sort, {"Project Name"}, {{
            "rows", each [
                base = Table.Buffer( _ ), 
                new_rows = List.Generate( 
                    ()=>0,each _<Table.RowCount(base),each _+1, 
                    each let current_row = base{_} in 
                    current_row & Record.FromList(
                        if _ = 0 then {null}
                            else let last_row = base{_-1} in
                            {Duration.Days( current_row[Report Date] - last_row[Report Date] )}, 
                        { Record.FieldNames( NewColType ){0} } 
                    ) 
                ) 
            ] [ new_rows], 
            type list
        }}, 
        GroupKind.Local 
    ),
    ToTable = Table.FromRecords( 
        List.Combine( Group[rows] ), 
        type table Type.ForRecord( Type.RecordFields( Type.TableRow( Value.Type(Source) ) ) & NewColType, false)
    )
in
    ToTable

     

    Tested with the below sample (generated with gemini):

    Sample (excerpt)

    Project Name Report Date Latest Report Days since report date
    Project A 2/24/2026 Yes 18
    Project A 2/18/2026 No 24
    Project A 1/9/2026 No 64
    Project A 1/2/2026 No 71
    Project A 10/9/2025 No 156
    Project A 9/30/2025 No 165
    Project B 12/15/2025 Yes 89
    Project B 12/12/2025 No 92
    Project C 1/12/2026 Yes 61
    Project C 12/4/2025 No 100
    Project C 10/12/2025 No 153
    Project C 10/2/2025 No 163
    Project C 9/15/2025 No 180
    ... ... ... ...
    Project BT 1/11/2026 Yes 62
    Project BT 12/6/2025 No 98
    Project BU 1/8/2026 Yes 65
    Project BU 1/6/2026 No 67
    Project BV 2/20/2026 Yes 22
    Project BV 1/3/2026 No 70
    Project BV 1/1/2026 No 72
    Project BV 12/23/2025 No 81

     

    Output

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    5 months ago

    Hi MarkLaf, 

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to blueredyellow, lbendlin, cengizhanarslan,  for those inputs on this thread.

    Has your issue been resolved? If the response provided by the community member blueredyellow, lbendlin, cengizhanarslan,  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      5 months ago

      Hi blueredyellow, 

      Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

      Thank you.