Forum Discussion
Need help in creatin calculated columns or DAX Measure
Hi Friends,
I have 2 source table to track the cost report submitted for any projects. In the master table, against each project it is marked Yes/No/NA to have reports for G2 or G5. Report table contains cost report reference for the project at G2 or G5 as per the tagging in the master.
The requirement here is to identify the missing reports and present as KPI. Here is short description orlogic to identigy the missing onces
Gate is to indicate which gate is this project
G2 Date/G5 Date: Planned Dates for respective gates
Report at G2/Report at G5 : Expected reports at this gates once gate is passed
Report at G2/Report at G5 - when value is no , not consider for any calculations. Need to ignore (Prj8)
G2 / G5 Ageing : Need to calculated ageing w.r.t today when gate is already passed and not found any report in reports table.
G2/G5 Availbility : Need to check the report availibility in the reports table against the project and Gate. If record present in Reports table mark it as Yes,else No. If Report at G2/G5 is NA, update the same here as well
Cost Report Required : Count of projects where Report at G2 is Yes and Gate >= G2 (same for G5)
Cost Report Available : Count of projects where Report at G2 is Yes and Gate >= G2 (same for G5) and corresponding report entry preent in Report table
Output tables
| Cost Report Required | Cost Report Available | % | |
| G2 | 7 | 3 | 42.9% |
| G5 | 5 | 3 | 60.0% |
| 12 | 6 | 50.0% |
| Prj | Gate | G2 Date | G5 Date | Report at G2 | Report at G5 | G2 Ageing | G5 Ageing | G2 Availibility | G5 Availibility |
| Prj1 | G2 | 4-Jan-26 | 4-Apr-26 | Yes | NA | Yes | |||
| Prj2 | G3 | 4-Jan-26 | 4-Apr-26 | Yes | Yes | Yes | |||
| Prj3 | G5 | 4-Jan-26 | 4-Feb-26 | NA | Yes | NA | Yes | ||
| Prj4 | G5 | 4-Jan-26 | 4-Feb-26 | Yes | Yes | 49 | No | Yes | |
| Prj5 | G5 | 4-Jan-26 | 4-Feb-26 | Yes | Yes | Yes | Yes | ||
| Prj6 | G5 | 4-Jan-26 | 4-Feb-26 | NA | Yes | 19 | NA | No | |
| Prj7 | G4 | 4-Jan-26 | 4-Apr-26 | Yes | NA | 19 | No | NA | |
| Prj8 | G2 | 4-Jan-26 | 4-Apr-26 | No | No | ||||
| Prj9 | G4 | 4-Jan-26 | 4-Apr-26 | NA | NA | NA | NA | ||
| Prj10 | G2 | 4-Feb-26 | 4-Apr-26 | Yes | Yes | 19 | No | ||
| Prj11 | G5 | 4-Feb-25 | 1-Feb-26 | Yes | Yes | 365 | 23 | No | No |
| Prj12 | G1 | 4-Mar-25 | 1-Oct-26 | Yes | Yes | NA | NA |
| Master Table | |||||
| Prj | Gate | G2 Date | G5 Date | Report at G2 | Report at G5 |
| Prj1 | G2 | 4-Jan-26 | 4-Apr-26 | Yes | NA |
| Prj2 | G3 | 4-Jan-26 | 4-Apr-26 | Yes | Yes |
| Prj3 | G5 | 4-Jan-26 | 4-Feb-26 | NA | Yes |
| Prj4 | G5 | 4-Jan-26 | 4-Feb-26 | Yes | Yes |
| Prj5 | G5 | 4-Jan-26 | 4-Feb-26 | Yes | Yes |
| Prj6 | G5 | 4-Jan-26 | 4-Feb-26 | NA | Yes |
| Prj7 | G4 | 4-Jan-26 | 4-Apr-26 | Yes | NA |
| Prj8 | G2 | 4-Jan-26 | 4-Apr-26 | No | No |
| Prj9 | G4 | 4-Jan-26 | 4-Apr-26 | NA | NA |
| Prj10 | G2 | 4-Feb-26 | 4-Apr-26 | Yes | Yes |
| Prj11 | G5 | 4-Feb-25 | 1-Feb-26 | Yes | Yes |
| Prj12 | G1 | 4-Mar-25 | 1-Oct-26 | Yes | Yes |
| ReportTable | |||
| Prj | ReportID | Gate | ReportDt |
| Prj1 | Rep1 | G2 | 4-Jan-26 |
| Prj2 | Rep2 | G2 | 4-Jan-26 |
| Prj3 | Rep3 | G5 | 4-Feb-26 |
| Prj4 | Rep4 | G5 | 4-Feb-26 |
| Prj5 | Rep5 | G2 | 4-Jan-26 |
| Prj5 | Rep5 | G5 | 4-Feb-26 |
Hi manojk_pbi ,
Thank you for reaching out to the Microsoft Community Forum.
Please refer below output snaps and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
3 Replies
Hi manojk_pbi ,
Thank you for reaching out to the Microsoft Community Forum.
Please refer below output snaps and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi manojk_pbi ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @manojk_pbi ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh