weekly updated report how to use latest entry for each week

[vozniak]

Greetings, I have a report that is updated weekly, but the users are allowed to make changes up until the deadline so there may be multiple entries for each item each week. I need to be able to sort out the most recent entry for each item each week. I can show the most recent item easily, but I can't get it to include every week of the month.

IsLatestUpdate =
Table1[Week Ending Date]
    = CALCULATE (
        MAX ( Table1[Completion time] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Functional Area] = EARLIER ( Table1[Functional Area]) )
    )

shows me true/false is this the latest update for that Functional Area. But it won't tell me each week of the month. How do I add an extra filter to give me the same information for each week?

 

[parry2k]

@vozniak can you share sample data to get the solution

[v-cherch-msft · Accepted solution]

Hi @vozniak

You may add Month column and Week column in query editor first. Then you may create a calculated column as below:

IsLatestUpdate =
Table1[Completion time]
    = CALCULATE (
        MAX ( Table1[Completion time] ),
        FILTER (
            Table1,
            Table1[Month] = EARLIER ( Table1[Month] )
                && Table1[Week of Month] = EARLIER ( Table1[Week of Month] )
                && Table1[Functional Area] = EARLIER ( Table1[Functional Area] )
        )
    )

Regards,

Cherie