Variance to prior weeks running sum

[Chris878]

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 Sum for all Sales as shown in the table. I want to show the development of the last 6 weeks in the table however include all the previous weeks as well for the cumulated measures. And, to make it more complicated, i need the calendar weeks displayed be able to be filtered (Data Status as of "12.12.2024") in order to replicate old week statuses. Therefore a date table is existent.

in the example there are no entries before 01.01.2025, however in my real data the revenue would for example start already in 31.10.2024. In the end, i need the collumns "Revenue" and "Variance to prior week REV/Guest".

	01.01.25	08.01.25	15.01.25	23.01.25	31.01.25
Revenue	5	10	5	10	5
Guests	2	1	2	1	2
Revenue per Guest	2,5	10	2,5	10	2,5
Cumulated Rev	5	15	20	30	35
Cumulated Rev/Guest	2,5	5	4	6	35/8
Variance to prior week REV/Guest		+2,5	-1	+2	-1,625

Can anyone help with the formula? I have been testing a lot but fail to find a solution in which the weeks are dynamicly shown in the table and the running sum is calculated based on that collumns without showing the collumns before the 6 weeks.

my current formular for the revenue is 

CALCULATE(SUM(Revenue.Table[Revenue]),DATESINPERIOD('Date Table Load Date'[Date],MAX('Date Table Load Date'[Date]),-42,DAY)),

for the collumns i have created a collumn in the Revenue.Table as "Date Last 6 Weeks" = RELATED ('Date Table Load Date'[End of Week]). A Filter for the 'Date Table Load Date'[Date] is placed on the page to have the dynamic date range of the 6 weeks

Thank you.

[v-csrikanth]

Hi @Chris878 
Thanks for confirming that issue has been resolved by using the above formula after your research.

If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[Chris878]

Hey everyone,

i ended up using this formula after some more research and used it in a calculation group. It works fine for me. the table "Previous week" is used to filter for a specific time of data to be included based on the "Load date". the both date tables are not connected to each other.

Current Year Last 6 Weeks =

VAR currentweek = MAX('Date Table Previous Date'[End of Week])

VAR previousweek = currentweek-42

VAR periodtodate = FILTER(ALLSELECTED('Date Table Load Date'[End of Week]),'Date Table Load Date'[End of Week]<=MAX('Date Table Load Date'[End of Week]))

VAR periodtodatepriorweek = FILTER(ALLSELECTED('Date Table Load Date'[End of Week]),'Date Table Load Date'[End of Week]+7<=MAX('Date Table Load Date'[End of Week]))

RETURN

    IF(VALUES('Date Table Load Date'[End of Week])<=currentweek && VALUES('Date Table Load Date'[End of Week])>previousweek,

    CALCULATE(SELECTEDMEASURE(),REMOVEFILTERS('Date Table Load Date'[End of Week]),periodtodate)

     -

    CALCULATE(SELECTEDMEASURE(),REMOVEFILTERS('Date Table Load Date'[End of Week]),periodtodatepriorweek))

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[lbendlin]

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)