Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

1.png

 

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:
2.png
 
What am I doing wrong?
 
Thanks!
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

1.png

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

1.png

 

amitchandak
Super User
Super User

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

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
Not applicable

Hi Amit,

 

This seems to experience the same issue I encountered:

3.png

 

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

 

Thanks for taking a look!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.