Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure Help - Previous Week Calculation

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!
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      amitchandak
      Super User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.