Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Avg. Weekly Run Rate

Hi All,   Side note: I haven't worked a lot with PBI and therefore don't have that much experience with DAX.   I created a measure in which I calculated the average weekly Run Rate:   Avg. We...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I fixed it myself by just substracting de amount of weeks that were used in the first quarter. Tried SUM, AVERAGE(X), didn't work for me. Maybe has to do with Process_Date[week], when filtering Q2, it works with the weeknumbers instead of calculating the weeks. Therefore substracting with a higher amount then it should. By substracting Q1 (13 weeks) it divides by the right amount. Same trick next quarter. 

     

    Avg. Weekly Run Rate Nieuw Contract =
    VAR WeeksWithSales = CALCULATE( MAX( Process_Date[Week] ); FILTER( ALLSELECTED( Process_Date ); [N] > 0 ) ) - 13
    var CumulativeTotal = CALCULATE( [Nieuw contract (cumulative)]; ALLSELECTED( Process_Date ) )

     

    RETURN
    DIVIDE( CumulativeTotal; WeeksWithSales; 0 )
     
     
Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-chuncz-msft Can you still help me? I don't understand what I'm doing wrong here. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I fixed it myself by just substracting de amount of weeks that were used in the first quarter. Tried SUM, AVERAGE(X), didn't work for me. Maybe has to do with Process_Date[week], when filtering Q2, it works with the weeknumbers instead of calculating the weeks. Therefore substracting with a higher amount then it should. By substracting Q1 (13 weeks) it divides by the right amount. Same trick next quarter. 

 

Avg. Weekly Run Rate Nieuw Contract =
VAR WeeksWithSales = CALCULATE( MAX( Process_Date[Week] ); FILTER( ALLSELECTED( Process_Date ); [N] > 0 ) ) - 13
var CumulativeTotal = CALCULATE( [Nieuw contract (cumulative)]; ALLSELECTED( Process_Date ) )

 

RETURN
DIVIDE( CumulativeTotal; WeeksWithSales; 0 )