Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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.
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.
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.
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.
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))
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |