cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruyaselman Regular Visitor
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)

datasource.JPG

 

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;
Dashboard.JPG
Please help...
 
Best Regards,
Ruya
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
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
            && Sheet1[Task] = EARLIER ( Sheet1[Task] )
    )
)

Then edit your measure.

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

Here is the result.

previous week.PNG

 

Best Regards,

Eads

 

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

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

View solution in original post

Community Support Team
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:Capture.PNG

Best Regards,

Eads

 

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

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

View solution in original post

4 REPLIES 4
Community Support Team
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
            && Sheet1[Task] = EARLIER ( Sheet1[Task] )
    )
)

Then edit your measure.

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

Here is the result.

previous week.PNG

 

Best Regards,

Eads

 

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

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

View solution in original post

Highlighted
ruyaselman Regular Visitor
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
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:Capture.PNG

Best Regards,

Eads

 

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

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

View solution in original post

ruyaselman Regular Visitor
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,

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,132)