cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luca_borome Regular Visitor
Regular Visitor

Average on the last week

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 

5 REPLIES 5
judspud Member
Member

Re: Average on the last week

Hi @luca_borome 

 

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

luca_borome Regular Visitor
Regular Visitor

Re: Average on the last week

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...
luca_borome Regular Visitor
Regular Visitor

Re: Average on the last 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))
 
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 ?
luca_borome Regular Visitor
Regular Visitor

Average of the last week

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 ?
Super User
Super User

Re: Average of the last week

Hi @luca_borome,

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
        )
    )

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 276 members 3,016 guests
Please welcome our newest community members: