Forum Discussion

Chris878's avatar
Chris878
Regular Visitor
1 year ago

Variance to prior weeks running sum

Hey,   i want to create a report showing revenue increases per calendar week (end of week) and the number of respective customer. I also want to calculate the KPI Revenue per Customer as running Su...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

First step is to bring your data into a usable format.

 

Then fix the column types

Now you can create your measures

 

Revenue per Guest = DIVIDE(sum('Table'[Revenue]),sum('Table'[Guests]))

Cumulated Rev = CALCULATE(sum('Table'[Revenue]),WINDOW(1,ABS,0,REL,allselected('Table'[Date])))

 

And this is where you need to stop and think - does Cumulate Rev/Guest even make sense?  What if these are the same guests?

 

 

Cumulated Rev/Guest = 
var w = WINDOW(1,ABS,0,REL,allselected('Table'[Date]))
return divide(CALCULATE(sum('Table'[Revenue]),w),CALCULATE(sum('Table'[Guests]),w))

 

Which doesn't match your expected result.

 

 

Anyway, moving on.

 

Variance = 
var p = CALCULATE([Cumulated Rev/Guest],OFFSET(-1,allselected('Table'[Date])))
return if(not ISBLANK(p),[Cumulated Rev/Guest]-p)