I hope you & your family are safe and healthy.
I am struggling since couple of hours with some calculations I am trying to onboard under the model attached (pbix file).
The raw data is not in an optimal format (i tried to unpivot it but then I was getting even harder to create the formulas…)
Thus so far I am struggling with the table names measures in which:
Current AIC GBP should be = Calculate the Spend PY GBP + YTD GBP - Part Cap PY GBP only for the project where the status has been either Closed or submitted mode otherwise I want a blank result) --> I manage to it based on only 1 filter (calculation 1) but the calculation v2 is not working…
Ranking based on the YTD spend = I tried 2 differents formulas but dont lead to the result expected unfortunately ( I want to get a ranking on every project based on their YTD value and if the value is the same then the rank should be the same one ) like a Rank.EQ in excel …
The calculation name current delay should basically = // calculating …if a project status is “closed” or “commissioned” then “not delayed”, otherwise if Planned date <= current month (so here I assume March) then “delayed” otherwise “not delayed” …clearly I dont know where to start to tackle this one
and the last one will be based on this Delayed or not Delayed from the above doing a calculation to evaluate the number of month in between like: if Delayed then rounding(Current Month - Plan date / 30,25) + (LV Date / 1000000), 0) – I managed to do it in excel but impossible here
Many many thanks if advance you will saved me couple of hours / day for sure.
JT Test.pbix (332.9 KB)