Dates Since last report

[blueredyellow]

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.....

[v-kpoloju-msft]

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]

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.

[MarkLaf · Accepted solution]

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

[cengizhanarslan]

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 )

[blueredyellow]

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!

[lbendlin]

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