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,
     
     
amitchandak's avatar
amitchandak
Super User
4 years ago

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

  • jcarlos's avatar
    jcarlos
    Helper I
    4 years ago

    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,