Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Average rolling turnover per store

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!

 

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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

5.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

1 REPLY 1
v-diye-msft
Community Support
Community Support

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

5.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.