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 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.2508.01.2515.01.2523.01.2531.01.25
Revenue5105105
Guests21212
Revenue per Guest2,5102,5102,5
Cumulated Rev515203035
Cumulated Rev/Guest2,554635/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.

Need Help

4 Replies

  • 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)

     

     

     

     

     

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Chris878's avatar
    Chris878
    Regular Visitor
    1 year ago

    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))
  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    1 year ago

    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.