Avg. Weekly Run Rate

[Anonymous]

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 🙂

[v-chuncz-msft]

@Anonymous ,

Research the code generated by Quick measure Rolling average and try using AVERAGEX.

[Anonymous]

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

[Anonymous · Accepted solution]

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]

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?