Forum Discussion
amaral_diego
Helper II
Helper IIManaging Overdue Projects in Power BI with Automatic Date Adjustment
In Power BI, I have a table that counts the number of projects that need to be completed by their due date. However, there is a need for projects that are not completed within the specified month to ...
Kedar_Pande
Super User
Super UserDataAjustada =
VAR DataInicial = f_Projetos[Termino]
VAR MesAtual = MONTH(TODAY())
VAR AnoAtual = YEAR(TODAY())
RETURN
IF (
f_Projetos[Status] <> "Completo" && f_Projetos[Status] <> "Despriorizado",
-- If the project is overdue, keep adjusting the date
IF (
DataInicial < TODAY(),
EDATE(DataInicial, DATEDIFF(DataInicial, TODAY(), MONTH)),
DataInicial
),
DataInicial -- If project is complete or deprioritized, don't adjust
)
ProjetosAtrasadosPorMes =
CALCULATE(
COUNTROWS(
FILTER(
f_Projetos,
f_Projetos[DataAjustada] <= TODAY() &&
f_Projetos[Status] <> "Completo" &&
f_Projetos[Status] <> "Despriorizado"
)
),
USERELATIONSHIP(d_Calendario[Date], f_Projetos[DataAjustada])
)
Ensure that you have a proper relationship between your project table (f_Projetos) and the date/calendar table (d_Calendario). Use USERELATIONSHIP in the measure to make sure the DataAjustada column drives the relationship for the project dates rather than the original due date.
If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn
amaral_diegoHelper II
Helper IIHi,
This part looks like it worked well,
DataAjustada =
VAR DataInicial = f_Projetos[Termino]
VAR MesAtual = MONTH(TODAY())
VAR AnoAtual = YEAR(TODAY())
RETURN
IF (
f_Projetos[Status] <> "Completo" && f_Projetos[Status] <> "Despriorizado",
-- If the project is overdue, keep adjusting the date
IF (
DataInicial < TODAY(),
EDATE(DataInicial, DATEDIFF(DataInicial, TODAY(), MONTH)),
DataInicial
),
DataInicial -- If project is complete or deprioritized, don't adjust
)
But this one below it seems not,
ProjetosAtrasadosPorMes =
CALCULATE(
COUNTROWS(
FILTER(
f_Projetos,
f_Projetos[DataAjustada] <= TODAY() &&
f_Projetos[Status] <> "Completo" &&
f_Projetos[Status] <> "Despriorizado"
)
),
USERELATIONSHIP(d_Calendario[Date], f_Projetos[DataAjustada])
)
with this code it's show me just one project delayed in October, I must see 6 projects delayed in October and 1 in November, and so on.