Forum Discussion
Showing only Last Week progress
Hi!
I am using Power BI to show a project status. The data comes from Primavera P6.
One of the important measures is related to the %Plan vs %Actual for last week/month.
I have created this measure for %Actual_Last_Week:
As you can see, calculates the progress by week/by category.
However, when I use the measure in a general table...shows values from other weeks:
This value comes from August 2021:
Thanks v-chenwuz-msft .
The relationship between my tables is like you are suggesting.
I've solved the issue by doing this:
%Actual_Last_Week =VAR var_MAX_Date = calculate(MAX('NLU Distribution - ACTUAL'[Week_Rank]),ALL())RETURNCALCULATE(PROJECT[%Actual_by_Category],'Project Calendar'[Week_Rank]=var_MAX_Date,all('Project Calendar'[Week_Rank]))Basically...declaring a VAR to define the MAX Date...removing ALL the filters to ensure MaxWeek it's the same for every category.Many thanks for your support,
5 Replies
jcarlos , You need date/week table with Week rank
new columns needed in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week = WEEKNUM([Date],2)
year Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatmeasures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Many thanks, amitchandak .
My date table already has Week_Start_Day and Week_Finish_Date.
I've followed your advice by adding the Week_Rank (which solves a big issue...because I didn't know how to add the chronological week number).
Regarding the measures: I have a 'NLU Distribution - ACTUAL' table, which has the historical transaction dates.
In my original measure, the parameter I've used is
'Project Calendar'[Date]=MAX('NLU Distribution - ACTUAL'[Actual_Date])
Now...I don't get how to write the parameter using the Weel_Rank. Do I need to add a Week_Rank column in my 'NLU Distribution - ACTUAL' table?
'Project Calendar' table
'NLU Distribution - ACTUAL' table.
Regards,
Following amitchandak advice...I've updated:
- Transactional table: I've added a column Week_Rank = RELATED('Project Calendar'[Week_Rank]), to "link" the transaction dates with the week number.
- Project Calendar: I've added a column Week_Rank = RANKX(ALL('Project Calendar'),'Project Calendar'[Week_Start_Day],,ASC,Dense), to add the chronological week number to my calendar
And I've done some additional measures trying to show only the %Actual of last calendar week, instead of the %Actual of last week with data.
1) %Actual_Last_Week = CALCULATE(
DIVIDE([ACTUAL_SUM_NLU],[SUM_Weightage_By_Date]),'Project Calendar'[Week_Rank]=MAXA('NLU Distribution - ACTUAL'[Week_Rank]))2)%Actual_Last_Week2 = IF(SUM('NLU Distribution - ACTUAL'[NLU_Actual])=BLANK(),0,calculate(DIVIDE([ACTUAL_SUM_NLU],[SUM_Weightage_By_Date]),'Project Calendar'[Week_Rank]=MAXA('NLU Distribution - ACTUAL'[Week_Rank])))In the end, the result is the same. It's like the measure "understand" as Last_Week, the latest week with information.Regards,Hi jcarlos ,
1 Set relationship cross filter direction is from 'Project Calendar' table to 'NLU Distribution - ACTUAL' table single and one to many.
2 try code like following :
%Actual_Last_Week = CALCULATE( DIVIDE( [ACTUAL_SUM_NLU], [SUM_Weightage_By_Date] ), WEEKNUM( 'Project Calendar'[date] ) = WEEKNUM( MAX( 'NLU Distribution - ACTUAL'[date] ) ) - 1 )if does not work fine, please share pbix file without sensitive data.
Best RegardsCommunity Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-chenwuz-msft .
The relationship between my tables is like you are suggesting.
I've solved the issue by doing this:
%Actual_Last_Week =VAR var_MAX_Date = calculate(MAX('NLU Distribution - ACTUAL'[Week_Rank]),ALL())RETURNCALCULATE(PROJECT[%Actual_by_Category],'Project Calendar'[Week_Rank]=var_MAX_Date,all('Project Calendar'[Week_Rank]))Basically...declaring a VAR to define the MAX Date...removing ALL the filters to ensure MaxWeek it's the same for every category.Many thanks for your support,
- Transactional table: I've added a column
