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. Weekly Run Rate Nieuw Contract =
VAR WeeksWithSales = CALCULATE( MAX( Process_Date[Week] ); FILTER( ALLSELECTED( Process_Date ); [Nieuw contract] > 0 ) )
var CumulativeTotal = CALCULATE( [Nieuw contract (**bleep**.)]; ALLSELECTED( Process_Date ) )

RETURN
DIVIDE( CumulativeTotal; WeeksWithSales; 0 )
 
When plotting this into a graph it works perfectly fine, however when I slice the date (for example showing only week 14 till 26 (Q1)), it calculates the run rate based on the last week with sales (actually, logically like I calculated in my measure with MAX). However, I want it to calculate only the weeks with sales (for the dates I sliced), instead of the last week with sales.
 
How should I formulate my measure? Or what am I doing wrong?
 
Hope you guys can help me with this :)
 
 
 
 

 

 

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 )
     
     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Maybe it's me, but I don't really understand what you're trying to say here? 

       

      I tried using AVERAGEX instead of MAX, however this didn't work.

       

      Could elaborate more please? 

       

       

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