Average on the last week

[Anonymous]

Hi,

I want to create a measure with a division :

- numerator = backlog 

-denominator = the average of the number created ticket of the last week (the calculation = all the creation of the week divided by 5)

My issue is on the denominator, I have some difficulties to create it. I don't know how can I create an weekly average.

Can you help me on that point ? 

Sincerely 

[Anonymous]

Hi all,

I want to create a  measure of the average of the last week, for example if I am in the 25th week of the year I want the average of the 24th week.

I found a way to calculate the average of the week (http://radacad.com/week-to-date-calculation-in-power-bi-with-dax) :

Week avg bis =

var CurrentDate=LASTDATE('Date bis'[Date])

var DayNumberOfWeek=WEEKDAY(LASTDATE('Date bis'[Date]);3)

return

CALCULATE(DIVIDE([Nb ticket];5);

DATESBETWEEN(

'Date bis'[Date];

DATEADD(

CurrentDate;

-1*DayNumberOfWeek;

DAY);

CurrentDate))

So can you help me to modify this measure for do that please ?

[AlB]

Hi @Anonymous,

Assuming the code you show it's working, this minot modification should do the job:

Week avg bis previous week =
VAR CurrentDate =
    LASTDATE ( 'Date bis'[Date] )
VAR DayNumberOfWeek =
    WEEKDAY ( LASTDATE ( 'Date bis'[Date] ); 3 )
RETURN
    CALCULATE (
        DIVIDE ( [Nb ticket]; 5 );
        DATESBETWEEN (
            'Date bis'[Date];
            DATEADD ( CurrentDate; ( -1 * DayNumberOfWeek ) - 7; DAY );
            CurrentDate - 7
        )
    )

[judspud]

Hi @Anonymous 

Assuming you have a date field along with each entry you can use a calculate function with filters to say the date created should be greater than today()-5 and less than today().

Hope this helps

Thanks,

George

[Anonymous]

Hi,

First thank you for your help.

Secondly, I try this : 

Avg creation weekly =

CALCULATE (

AVERAGEX( 'All detail';[Nb ticket]);'All detail'[Inc Date Creation]>= TODAY()-5 && 'All detail'[Inc Date Creation]<TODAY()

)

But all the result are = 1,00 

Measure ''Nb ticket''  = count(N°Ticket)

I dont understand why I always have this result...

[Anonymous]

I found a way to calculate the average of the week (http://radacad.com/week-to-date-calculation-in-power-bi-with-dax) :

Week avg bis =

var CurrentDate=LASTDATE('Date bis'[Date])

var DayNumberOfWeek=WEEKDAY(LASTDATE('Date bis'[Date]);3)

return

CALCULATE(DIVIDE([Nb ticket];5);

DATESBETWEEN(

'Date bis'[Date];

DATEADD(

CurrentDate;

-1*DayNumberOfWeek;

DAY);

CurrentDate))

I want the average of the last week, for example if I am in the 25th week of the year I want the average of the 24th week.

So can you help me to modify this measure for do that please ?