Table of Contents

Change Log

2024.02.20

  • Introduced an updated suite of reports to make the report deployment process easier

2023.10.20

  • Hygiene Report added
  • Dim_Resources.json updated to include additional items needed for the Hygiene report.

2023.10.10

  • A bug was found in the Dim_Date dataflow for the Jan - Dec Financial year end. There were some incorrect calculations to the Financial Start and Financial End dates.
  • Added a new Program KPI Status that uses ISINSCOPE dax formula so the KPI does not appear in the total row for the Program Overview Tab
 measure 'Program KPI (inscope)' =
  
  CALCULATE (
      SUMX (
          Fact_Changestatus_reworked_Program,
          Fact_Changestatus_reworked_Program[KPI]
      ),
      Fact_Changestatus_reworked_Program[KPIType] = "Project KPI",
      Fact_Changestatus_reworked_Program[ProgramCommentRN] = 1
  )
  displayFolder: "Program KPI"
  KPI:
   statusExpression:=
    VAR Result =
        SWITCH (
            TRUE,
            [Program KPI (inscope)] =  3, -1,
            [Program KPI (inscope)] =  2, 0,
            [Program KPI (inscope)] = 1, 1
        )
    
    RETURN
    IF ( ISINSCOPE (Dim_Program_Bridge[ProgramName]),Result,BLANK())
   statusGraphic: Thermometer
  lineageTag: 1f115638-69eb-468d-bce2-21f38f669fcd
  • Added Portfolio Count to ensure all portfolios are visualised in the Portfolio Overview page
 measure '# Count of Portfolios' = COUNTROWS(Dim_Portfolio_Bridge)
  displayFolder: Portfolio
  formatString:= 0
  lineageTag: 9a6661f9-dfe7-4907-9fa6-cb91466a7b67
  • Used the Count of Programs measures to ensure all programs are visualised the Program Overview page.
  • Adjusted the Resource_Committed_Rule to check if a resource has been terminated.
 column Resource_Committed_Rule =
  SWITCH(TRUE(),
  (zSummary_Resource_Capacity[BookedHours] <> 0 || zSummary_Resource_Capacity[CommittedHours] <> 0) && zSummary_Resource_Capacity[IsTerminated] <> 1,1,0)
  dataType: Int64
  formatString:= 0
  isDataTypeInferred: false
  lineageTag: 477e1e87-14d4-4ce5-98d4-31ad7b4aa0ed
  summarizeBy: Sum

  annotation SummarizationSetBy = Automatic

2023.07.27

  • The Resources model was retested again for Quality Assurance and a bug was discovered on the timesheet entity in PowerBI. Sell and Cost rates for timesheets in the future were not flowing through.

  • To update the code directly please see below, this updated on the end of the LEFT JOIN for the sell rate and the cost rate.

  • RSR = Resource Sell Rate

    Isnull(rsr.sensei_effectivedateoffset,Dateadd(Year,100,Dateadd(hour, 10, Getdate())))

  • RCR = Resource Cost Rate

    Isnull(rcr.sensei_effectivedateoffset,Dateadd(Year,100,Dateadd(hour, 10, Getdate())))

2023.06.15

  • A bug was found on the Dim_Projects dataflow. Programs with no Projects with related Portfolios were also appearing in the sensei_portfolio (no projects) query.

    • The fix for this is that there are two additional steps in the sensei_program (no projects) query. You may be able to copy the below code and deploy it in a dataflow already created.
    #"Merged queries 6" = Table.NestedJoin(#"Reordered columns", {"sensei_portfolio"}, #"sensei_portfolio (no projects)", {"sensei_portfolio"}, "sensei_portfolio (no projects)", JoinKind.LeftAnti),
#"Removed columns 1" = Table.RemoveColumns(#"Merged queries 6", {"sensei_portfolio (no projects)"})
in
#"Removed columns 1"

  • There was a bug found in the Dim_Resources dataflow. Named and Generic Resources required where null value was observed in Sensei_PrimaryRoleName, to get the value from Sensei_Name.

    • You may be able to copy the below code and deploy it in a dataflow already created. The code below was placed between line 6 and line 14 in the Power Query Advanced Editor:
      #"Replaced value" = Table.ReplaceValue(
  #"Expanded systemuser(sensei_user)", 
  each [sensei_primaryrolename], 
  each if [sensei_primaryrolename] = null then [sensei_name] else [sensei_primaryrolename], 
  Replacer.ReplaceValue, 
  {"sensei_primaryrolename"}
),

  • The Portfolio, Projects, Portfolio Intake, Portfolio Innovation, Resources report files all have new help images on each page.

  • Updated some date columns visualisations settings in the Portfolio Model.

2023.06.02

  • A bug was found on the Dim_Projects dataflow. Portfolios with no projects assigned are required to be appended to the Dim_Projects table.
  • The best way to import this into an existing dataflow is the following:
    1. Import this new JSON into a new and separate dataflow
    2. Use the updating strategy aforementioned on this page. Copy the following two tables from the new imported dataflow (Ctrl + C)
    • sensei_portfolio (no projects) - Fakeguids (portfolio)
    1. Paste the two tables in the original dataflow and they should appear as new tables (Ctrl + V)
    2. Add in any additional columns you may have created on the sensei_portfolio (no projects) table
    3. Ensure the columns are in the same order as the final table
    4. Append this new table on the Dim_Project after Append 3
    5. Save and refresh the dataflow
    6. Refresh the model

2023.05.31

  • Updated the relationship between Dim_Tasks and Fact_Tasks in the Portfolio_Model to crossfilter both directions.
  • Updated Report filtering on the Projects Report.

2023.05.30

  • Released the BETA reports into Production.

2023.05.18

  • All Power BI templates and JSONs are now versioned controlled from Product Dev.
  • Released the entire Beta Reporting Suite as the current Demo 1 version.

2023.05.08

  • Bug found in the dimension table; Dim_Projects - external project table was duplicating.
  • Updated parameters for Decisions, Risk and Change Request dataflows.
  • Updated relationships between the Program - (Bridging Table) and KeyDates Program tables on the Portfolio Model.

2023.04.28

  • Entire reporting suite has been released.
  • Work Model introduced and separated from Portfolio model due to RLS requirements with the new embedding strategy.
  • New data flow JSONs released. (This ensure any reuse of data is centralised to minimise duplication).

2023.04.14

  • Bug found on the Portfolio Report with an incorrect page name spelling.
  • Incomplete relationship between the Fact_Tasks entity and the Dim_Resources entity in the Portfolio Model. Ticked the option to pass through secutiy filters.
  • Portfolio Innovation released.

2023.03.31

  • Bug found on the Risk Matrix on the Projects Report, updated with correct area mapping, the updated area mapping is used on the Portfolio reports.
  • Progress and Status are now highlights on the Projects Report, cross filtering any projects that are on hold is available, this is driven by the Dim_Projects data flow.
  • Changes done on the All-Projects report page, comment boxed removed, visual tool tip reintroduced.
  • Clicking on a Project Name/Program Name/Portfolio Name where the name is underlined in a visual will take you to the latest PSU- this supported via an updated Dim_Projects flow.
  • Portfolio Overview and Program Overview pages have been changed to include trending graphs for the status for both sub-programs and sub-projects.
  • Portfolio Tracking and Program Tracking pages have been reworked to include a Risk Matrix for sub-programs and sub-projects, other register information is visible via buttons on the page.
  • Fiscal Period is now included on the Dim_Date dataflow, this is driven by the app, the Dim_Date dataflow will need to be refreshed when clients enter new fiscal period range.
  • Portfolio Intake is released, this connects the Resources model.

2023.03.20

  • Dim_timesheets json now includes the fiscal year that is driven by the app
  • Dim_Projects json now includes projects that are required for non project time
  • Resource Model ready for beta review
  • Work and Resource (–client customisations available. Still finalizing the visuals) report ready for beta review

2023.03.02

  • Updated the dim_projects dataflow to only include active proposals
  • Added KPI ordering to the model and reports
  • Portfolio Status UI uplift, comments no longer in a tool tip
  • All projects UI uplift, comment no longer in a tool tip
  • Colour homogenisation across both reports
  • Updated the Dim_ChartofAccounts and assigned a Parent Category when there is no category

2023.02.16

  • Reviewed and updated the installation instructions
  • Set parameters where all the dataflows are used inside the Portfolio Model.

2023.02.07

  • DIM_Projects.json: Updated the hardcoded instance URLs to parameters

2023.01.11

  • Updated the relationships to both ways between task/resource/project entities for correct filtering
  • Changed visual errors as picked up by QA team
  • Increased the font size of the risk matrix
  • Updated Project Finish Date in dim_projects.json to match the app

New Relationship Diagram

Image shows the reporting data relationship diagram

2022.12.08

PBI Icon

Portfolio and Projects:

  • Updated Data Model
  • Shared Data Model between reports
  • Row Level Security
  • Increased Refresh Times
  • Easier to customise
Edit this page