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