1. ACTIVITY LIST
1.1. Pages
2. CHECK LIST
2.1. Pages
3. GOAL FULFILMENT FORECAST FY21
3.1. Pages
4. TENTATIVE
4.1. Pages
5. ACTUAL RESULTS OVERVIEW
5.1. Pages
6. CATEGORY CASE OVERVIEW
6.1. Pages
7. FORUMULAS
7.1. DATE TABLE
7.1.1. DATE table = CALENDAR(DATE(2017 ,1 ,1),DATE(2025 ,08 ,31))
7.2. FISCAL YEAR
7.2.1. Fiscal Year = {"FY17","FY18","FY19","FY20","FY21","FY22","FY23","FY24","FY25"}
7.3. DISTINCTCOUNT
7.3.1. Count All Activities = DISTINCTCOUNT(Activity[Activity No]) // Counts all distinct Activities in the Original table
7.4. REFRESH DATE (TABLE)
7.4.1. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}), #"Added Refresh Date" = Table.AddColumn(#"Changed Type", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone), #"Removed Columns" = Table.RemoveColumns(#"Added Refresh Date",{"Column1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Refresh Date", type datetime}}) in #"Changed Type1"
7.5. COUNT WITH FILTER
7.5.1. Count Completed & Not Rev. Activities = //Counts all activities in status Completed, that has not been fully reviewed CALCULATE(DISTINCTCOUNT(Allocation[Activity No]),FILTER(Activity, Activity[Project Status ID] = "6"), FILTER(Allocation, Allocation[Reviewed Check FY20] = 0 || Allocation[Reviewed Check FY20] = 1 ))
7.6. SUMX
7.6.1. Achieved cost saving EUR FY22 (If rev.) = SUMX('Savings Allocation For FM',IF('Savings Allocation For FM'[Is Reviewed Acheived cost saving] = TRUE(),CALCULATE([Total Achieved Cost Savings EUR], 'Savings Allocation For FM'[Fiscal Year (FM)] = "FY22"),BLANK()))
7.7. SUMX AND &&
7.7.1. Contracted saving (Σ) EUR FY21 (If rev.) = SUMX('Savings Allocation For PM', IF('Savings Allocation For PM'[Is Reviewed Cost Reduction] = TRUE() && 'Savings Allocation For PM'[Is Reviewed Cost Avoidance] = TRUE(), CALCULATE([Total Contracted Saving (Σ) EUR], 'Savings Allocation For PM'[Fiscal Year (PM)] ="FY21"), IF('Savings Allocation For PM'[Is Reviewed Cost Reduction] = TRUE() && 'Savings Allocation For PM'[Is Reviewed Cost Avoidance] = FALSE(), CALCULATE([Total Cost Reduction EUR], 'Savings Allocation For PM'[Fiscal Year (PM)] ="FY21"), IF('Savings Allocation For PM'[Is Reviewed Cost Reduction] = FALSE() && 'Savings Allocation For PM'[Is Reviewed Cost Avoidance] = TRUE(), CALCULATE([Total Achieved Cost Savings EUR], 'Savings Allocation For PM'[Fiscal Year (PM)] ="FY21"), BLANK() ))))
7.8. DIVIDE
7.8.1. PM Budget FY22 / Goal FY21 (Country) = DIVIDE([PM Input to Budget FY22],SUM('Goals - Country'[Contracted saving]))
7.9. CALCULATE
7.9.1. Count Activity (AcPlCo) = CALCULATE(COUNT(Activity[Activity No]),FILTER('Project Status','Project Status'[Project Status]="Active" || 'Project Status'[Project Status]="Planned" || 'Project Status'[Project Status]="Completed"))
7.10. RELATED
7.10.1. Total Alloc PM Baseline EUR = SUMX(Allocation, Allocation[PM Baseline]*RELATED('Currency Consolidation rate FY21'[Exchange rate]))
7.11. VAR CHECK DIFF
7.11.1. FM Diff Country = VAR CheckDiff = [Goal Country Actual Cost Savings EUR] - [FM Goal fulfilment FC EUR FY21] VAR NoFilter = SELECTEDVALUE('Goals - Country'[Country]) return IF(NoFilter = BLANK(), "", IF(CheckDiff<0 , "You reached the goal - keep up the good work", CheckDiff))
7.12. RANKX
7.12.1. Rank FM = var ChekcFilter = SELECTEDVALUE('Goals - Country'[Country]) var CountryRank = RANKX(ALL('Goals - Country'), [% FM goal fulfiled (country)]) Return IF(ChekcFilter = BLANK(), BLANK() , CountryRank)
7.13. IF(AND
7.13.1. Forecast Cost Saving Check = // used in BN report (FC CS >= baseline). checks if the saving is eaqual or bigger than the baseline, baseline must be bigger than zero IF(AND([Total Forecasted Cost Savings EUR] >= [Total Alloc FM Baseline EUR], [Total Alloc FM Baseline EUR] > 0), "CHECK", "OK" )
7.14. MAX AND USERELATIONSHIP
7.14.1. Project End Date Passed = CALCULATE(IF(MAX(Activity[Project End Date])<=MAX('DATE table Project date'[Date]),"Passed","Active"),USERELATIONSHIP(Activity[Project End Date],'DATE table Project date'[Date]))
7.15. ALLSELECTED
7.15.1. ALLSELECTED