Measure Help - Previous Week Calculation

[Anonymous]

Hello,

I've reviewed quite a few threads on PW calculations but can't seem to get mine to work.  I've got a single table where I added a custom column to show the week starting date: 
WeekStart = JCCD[ActualDate]- WEEKDAY(JCCD[ActualDate],1)+1

I have the following table layout:

An my previous week calculation is:

PWM = CALCULATE([AH], FILTER(ALLSELECTED(JCCD), JCCD[WeekStart] = [mDate]))
mDate = MAX(JCCD[WeekStart]) - 7

If I drop mDate into the table, I see the context is correct, but the cal doesn't come out:

What am I doing wrong?

Thanks!

[Anonymous · Accepted solution]

I finally figured it out by doing the following:

1. Created a calendar table

2. Changed the column on the matrix from the fact table to the dimension table (calendar)

3. Created the following formula.

    Prior Week =
          CALCULATE(SUM('JCCD'[ActualHours]), Filter(ALL('Calendar'), 'Calendar'[Week Start] = max('Calendar'[Week Start]) - 7))

I was close before, just needed the calendar table for the filtering to work and changing the column as mentioned in #2.  Now it looks like it should.

[amitchandak]

Create a date table and all your week, month calculation should be there. As long as you are grouping data on week, a 7  days behind measure should also work

//In date table
Week Start date = DATEADD('Date'[Date],-1*WEEKDAY('Date'[Date])+1,DAY)
Week End date = DATEADD('Date'[Date],7-1*WEEKDAY('Date'[Date]),DAY)

WM(This week) = CALCULATE([AH], FILTER(JCCD, JCCD[ActualDate] <=maxx(date,date[week start date]) && JCCD[ActualDate] >=minx(date,date[week end date])))

PWM = CALCULATE([AH], FILTER(JCCD, JCCD[ActualDate] <=maxx(date,dateadd(date[week start date],-7,day)) && JCCD[ActualDate] >=minx(date,dateadd(date[week end date],-7,DAY))))

PWM (7 day behind)= CALCULATE([AH], dateadd(date[date],-7,DAY))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[amitchandak]

Another way but will work on week level

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)

Measure = CALCULATE([AH], all('Date'),filter('Date','Date'[Week Rank]=max('Date'[Week Rank])-1))

[Anonymous]

Hi Amit,

This seems to experience the same issue I encountered:

Here is the file if you want to take a look: Previous Week Calc 

Thanks for taking a look!