Forum Discussion

jcarlos's avatar
jcarlos
Helper I
4 years ago
Solved

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 ...
Need Help
  • jcarlos's avatar
    jcarlos
    4 years ago

    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,
     
     
jcarlos's avatar
jcarlos
Helper I
4 years ago

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's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
      jcarlos
      Helper I
      4 years ago

      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,