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

[ruyaselman]

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;

Pbix -> https://1drv.ms/u/s!Am1a0vena5XKbMJpV2AtPdZ26Bg?e=3fr6LA

Data Source -> https://1drv.ms/x/s!Am1a0vena5XKbU2nJby6ZgWXqks?e=CkB11V

Please help...

Best Regards,

Ruya

[v-eachen-msft · Accepted solution]

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.

Best Regards,

Eads

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

[v-eachen-msft · Accepted solution]

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.

Best Regards,

Eads

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

[ruyaselman]

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;

[v-eachen-msft · Accepted solution]

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,

Eads

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

[ruyaselman]

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,