cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## How to calculate the Average Performance for the previous entry vs latest entry

Dear All,

I have a datasource, where we insert updates by weekly (every week have an increase in task progress and we instert it with relevant date)

I need to calculate the progress deviation between the latest entry in the system and the previous week.

So I created a measure to calculate the AVG Progress for the Latest Date (which is 5th August) ->

Progress for Last Date = CALCULATE(AVERAGE(Sheet1[Progress]),LASTDATE(Sheet1[Date]))

I also calculated the Latest Date (which is 5th of August)-> Last Submission Date = CALCULATE(LASTDATE(Sheet1[Date]))
Previous Week Date (which is 29th of July) -> Previous Week Submission Date = CALCULATE(LASTDATE(Sheet1[Date])-7)

The next step for me is to Calculate the Average Progress for the previous week date.
Im already calculating and finding which day it is, however I couldnt calculate the performance for that day.

Kindly find below how Dashboard looks & links;

Best Regards,
Ruya
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Hi @ruyaselman ,

You need to create a new column.

```Previous Week =
CALCULATE (
FIRSTNONBLANK ( Sheet1[Progress], 1 ),
FILTER (
Sheet1,
Sheet1[date]
= EARLIER ( Sheet1[date] ) - 7
)
)
```

```Progress for Previous Date =
CALCULATE ( AVERAGE ( Sheet1[Previous Week] ) )
```

Here is the result.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Hi @ruyaselman ,

You can create a new column in  "Original DS".

```Previous Week =
VAR a =
CALCULATE ( LASTDATE ( 'Original DS'[PrgDate] ), ALL ( 'Original DS' ) )
RETURN
IF ( 'Original DS'[PrgDate] = a, BLANK (), 'Original DS'[PrgDate] )```

Then edit your meaure "Progress for Previous Date Original DS"

```Progress for Previous Date Original DS =
CALCULATE(AVERAGE('Original DS'[Task Progress %]),LASTDATE('Original DS'[Previous Week]))```

Here is the result:

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Hi @ruyaselman ,

You need to create a new column.

```Previous Week =
CALCULATE (
FIRSTNONBLANK ( Sheet1[Progress], 1 ),
FILTER (
Sheet1,
Sheet1[date]
= EARLIER ( Sheet1[date] ) - 7
)
)
```

```Progress for Previous Date =
CALCULATE ( AVERAGE ( Sheet1[Previous Week] ) )
```

Here is the result.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Hi Again @v-eachen-msft.,

Thank you so much for your response. I tried in my original data source however it didnt work 😞

Because I have some additional columns in my Data Source, where I have relations.

Actually I`m filtering my tasks, based on Channel-Location-Owner & Component Levels. Also BackEnd/FrontEnd is another parameter for filtering. With this way, each Team provide their tasks.

Same Tasks are applicable to different Teams and different Locations and different Components Actually. So you will see many replicated records. Adding different columns is the only way to segregate in the Date Source.

I need to calculate the previous progress for a specific Channel, Location & a Team, so while I`m calculating this I need to consider other fields :S  I think I need to add some other filters to specify the calculation however I couldnt sort it out.

Kindly find below the pbix with original DS;

Community Support Team

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Hi @ruyaselman ,

You can create a new column in  "Original DS".

```Previous Week =
VAR a =
CALCULATE ( LASTDATE ( 'Original DS'[PrgDate] ), ALL ( 'Original DS' ) )
RETURN
IF ( 'Original DS'[PrgDate] = a, BLANK (), 'Original DS'[PrgDate] )```

Then edit your meaure "Progress for Previous Date Original DS"

```Progress for Previous Date Original DS =
CALCULATE(AVERAGE('Original DS'[Task Progress %]),LASTDATE('Original DS'[Previous Week]))```

Here is the result:

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: How to calculate the Average Performance for the previous entry vs latest entry

Dear @v-eachen-msft ,

Thank you so much for your support and immediate response. With this function it worked.

Just to understand, why did we need the Variable? Could you please tell? I never used it before actually.

Best Regards,

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,838)