Showing only Last Week progress

[jcarlos]

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:

%Actual_Last_Week = CALCULATE(

[%Actual_by_Category],filter(all('Project Calendar'[Date]),'Project Calendar'[Date]=MAX('NLU Distribution - ACTUAL'[Actual_Date])))

The measure works (almost) perfectly: 

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:

I have two questions:

Why does my measure do this?

How could I fix it?

Regards,

[jcarlos]

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,

[v-chenwuz-msft]

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 Regards

Community 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.

[jcarlos · Accepted solution]

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())

RETURN

CALCULATE(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,

[amitchandak]

@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 format

measures
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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[jcarlos]

Many 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,