topic Managing Overdue Projects in Power BI with Automatic Date Adjustment in DAX Commands and Tips

Managing Overdue Projects in Power BI with Automatic Date Adjustment

amaral_diego — Wed, 09 Oct 2024 17:58:39 GMT

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 automatically move to the next month. For example, a project scheduled for January, if not completed by the due date, should automatically move to February, and so on, until I update it in my data source.
Exemple
If the date is September 09/24 and it has to go to October 10/24, however if any date appears October 10/24 it will go to November 11/2024 but the date of October 10/24 that came from September 09/24 will remain in October 10/24 and it will only go to the next month, case 11, if the current month it is in ends and does not update the new date in the database, and this behavior must always be repeated.

I've tried this way below

Created this Calculated Column

DataAjustada =

VAR DataInicial = f_Projetos[Termino]

VAR UltimaDataExpirada = MAXX(FILTER(f_Projetos, f_Projetos[Termino] < TODAY()), f_Projetos[Termino])

RETURN

IF(

DataInicial < TODAY(),

EDATE(UltimaDataExpirada, 1),

BLANK()

)

And this measure below

ProjetosAtrasadosPorMes =

CALCULATE(

COUNTROWS(

FILTER(

ADDCOLUMNS(

f_Projetos,

"DataAjustadaFinal",

IF(

COUNTROWS(

FILTER(

f_Projetos,

f_Projetos[DataAjustada] = EDATE(f_Projetos[DataAjustada], 1)

)

) > 0,

f_Projetos[DataAjustada],

EDATE(f_Projetos[DataAjustada], 1)

)

f_Projetos[Termino] < TODAY() &&

f_Projetos[Status] <> "Completo" &&

f_Projetos[Status] <> "Despriorizado" &&

[DataAjustadaFinal] >= EDATE(f_Projetos[Termino], 1)

)

USERELATIONSHIP(d_Calendario[Date], f_Projetos[DataAjustada])

)

But, it's not working properly, it's all in November, in my case it would have to have some in October too, does anyone have any adjustment ideas?

Re: Managing Overdue Projects in Power BI with Automatic Date Adjustment

Kedar_Pande — Wed, 09 Oct 2024 18:02:58 GMT

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
)

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

Re: Managing Overdue Projects in Power BI with Automatic Date Adjustment

amaral_diego — Thu, 10 Oct 2024 14:21:51 GMT

Hi,
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.