The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi all,
I'm trying to write a formula for the average rolling turnover per store per year/ quarter/ month/ day.
I've gotten quite far already:
Average rolling turnover per store= Var rollingturnover= IF ( TODAY () >= FIRSTDATE ( Timevariables[Date] ); CALCULATE ( (Transactionvariables [Turnover]); DATESINPERIOD(Timevariables[Date]; LASTDATE(Timevariables[Date]); -1; YEAR) )) Var storecount= CALCULATE( DISTINCTCOUNT(Storelocation[storename]); ALLSELECTED(Transactionvariables[article numbers]); Crossfilter(Storelocation[storenumber];Transactionvariables[Storenumber];Both)) RETURN Divide(rollingturnover;storecount)
The part for rolling turnover works great. It gives the rolling turnover for the last year as long as I filter for a certain date (since I based the formula on lastdate). In other words, for whatever date I specify, it gives me the rolling turnover for that specific date.
The part for amount of stores works as well,. It gives me the amount of different stores that sold products within the selected period.
Problem:
Once I divide the rolling turnover with storecount~, something goes wrong: the amount of stores from the last selected period is used.
Simplified example:
Year - Month - Total month turnover - Amount of stores
2018 January € 100 in 10 stores
2018 February € 50 in 9 stores
2018 March € 100 in 10 stores
2018 April € 300 in 11 stores
2018 May € 100 in 10 stores
2018 June € 100 in 10 stores
2018 July € 100 in 10 stores
2018 August € 100 in 10 stores
2018 September € 100 in 10 stores
2018 October € 100 in 10 stores
2018 November € 100 in 10 stores
2018 December € 100 in 10 stores
Total turnover 2018 = € 1350. Total amount of stores varied between 9 and 11.
Average monthly turnover 2018 =((€ 100*10)+€ 50+€ 300)/ 12 = € 112,50
Average monthly turnover PER STORE for 2018 = ((€ 100/10)*10 + (€ 50/9) + (€ 300/11))/12 =€ 11,07
My current formula takes the full year's (rolling) turnover (€ 1350) and divides it by the amount of stores selected by the last store amount within the selected filter. In this case December = 10 stores. According to this formula my average montly turnover would be = (€ 1350/12)/10 stores = € 11,25 per store. And that is incorrect (should be 11,07).
What I want to do is to divide each monthly turnover (or quarter) by the corresponding amount of stores that were active for each period.
Can anyone help me fix/complete my formula? Would be much appreciated!
Solved! Go to Solution.
Hi @Anonymous
Please kindly let me know if you'd like to get below results:
Average monthly turnover 2018 = var a = SUMX(ALL('Table'),[Total month turnover]) Return DIVIDE(a,12)
Measure = var a = DIVIDE(MAX('Table'[Total month turnover]),MAX('Table'[Amount of stores])) Return a
Average monthly turnover PER STORE for 2018 = SUMX(ALL('Table'),[Measure])/12
Hi @Anonymous
Please kindly let me know if you'd like to get below results:
Average monthly turnover 2018 = var a = SUMX(ALL('Table'),[Total month turnover]) Return DIVIDE(a,12)
Measure = var a = DIVIDE(MAX('Table'[Total month turnover]),MAX('Table'[Amount of stores])) Return a
Average monthly turnover PER STORE for 2018 = SUMX(ALL('Table'),[Measure])/12
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
157 | |
137 | |
131 | |
81 | |
61 |