Forum Discussion

amaral_diego's avatar
amaral_diego
Helper II
1 year ago
Solved

How to Automatically Move Unfinished Projects to the Next Month in Power BI

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi amaral_diego ,

     

    I update the measure, you can try this one.

    AjustarData =
VAR _Today =
    TODAY ()
VAR _DATEDIFF =
    DATEDIFF ( 'Table'[Termino], TODAY (), DAY )
VAR _DATE1 =
    EOMONTH ( TODAY (), -1 ) + DAY ( 'Table'[Termino] )
VAR _DATE2 =
    EOMONTH ( _DATE1, 0 ) + DAY ( 'Table'[Termino] )
RETURN
    IF (
        'Table'[Status] = "Complete",
        'Table'[Termino],
        IF (
            'Table'[Termino] < _Today,
            IF ( _DATE1 < TODAY (), _DATE2, _DATE1 ),
            'Table'[Termino]
        )
    )

    Result of my sample is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Kaviraj11's avatar
Kaviraj11
Solution Sage
1 year ago

Hi,

Here’s a general approach to get you started

 

  1. Add a Status Column: Ensure your data source has a column indicating whether a project is completed or not.

  2. Create a Calculated Column

    Here’s an example:

    AdjustedDueDate = 
IF(
    [Status] = "Completed", 
    [DueDate], 
    EOMONTH([DueDate], 1) + 1
)

    3. Create a Measure for Counting Projects

     

    Here’s an example measure:

    ProjectsDueThisMonth = 
CALCULATE(
    COUNTROWS(ProjectsTable),
    FILTER(
        ProjectsTable,
        YEAR([AdjustedDueDate]) = YEAR(TODAY()) &&
        MONTH([AdjustedDueDate]) = MONTH(TODAY())
    )
)

     

amaral_diego's avatar
amaral_diego
Helper II
1 year ago

I've tried this way below

Created this Calculated Column

DataAjustada =
VAR DataInicial = f_Projetos[Termino]
VAR MesesAdicionados = DATEDIFF(DataInicial, TODAY(), MONTH) + 1
RETURN
IF(
DataInicial < TODAY(),
EDATE(DataInicial, MesesAdicionados),
DataInicial
)

and this measure below

ProjetosAtrasadosPorMes =
CALCULATE(
COUNTROWS(
FILTER(
f_Projetos,
f_Projetos[Termino] < TODAY() &&
f_Projetos[Status] <> "Completo" &&
f_Projetos[Status] <> "Despriorizado" &&
f_Projetos[DataAjustada] >= EDATE(f_Projetos[Termino], 1)
)
),
USERELATIONSHIP(d_Calendario[Date], f_Projetos[DataAjustada])
)
It's almost working, the thing is, it showing the information on November column, 
In my case, it should appear in October. It should move the overdue project to the following month, and here in my case the overdue projects are from September.

Could help me to solve this

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    1 year ago

    Can you update with this 

     

    Here’s a revised version of your calculated column:

    DataAjustada =
VAR DataInicial = f_Projetos[Termino]
VAR MesesAdicionados = DATEDIFF(DataInicial, TODAY(), MONTH) + 1
RETURN
IF(
    DataInicial < TODAY(),
    EOMONTH(DataInicial, MesesAdicionados - 1) + 1,
    DataInicial
)
    • amaral_diego's avatar
      amaral_diego
      Helper II
      1 year ago

      Hi, I’ve tried using this code, but unfortunately, the issue persists. It continues to show the information for November instead of October.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi amaral_diego ,

         

        I think you may use multiple tables in your calculation and your data model is complex with inactive relationships.

        Here I suggest you to show us your data model and what tables look like. You can share a sample file with us and show us a screenshot with your error or the result you want. This will make it easier for us to find the solution.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.