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.

     

amaral_diego's avatar
amaral_diego
Helper II
1 year ago

I also have tried with M language, with this function below

(AjustarData as nullable date) as nullable date =>
let
DataAtual = DateTime.LocalNow(),
NovaData = if AjustarData <> null and AjustarData < DataAtual then Date.AddMonths(AjustarData, 1) else AjustarData
in
NovaData

Then I go to "add column" and then "Invoke Custom Function"

So I select the function I created and then it gives me the option to enter a date, or select a column. If I go by the column or select the column that has the end date "Termino", it creates the column but without being in the date format and with "Error" in all the lines. This is the code that is in the column.

= Table.AddColumn(#"Erros Substituídos", "Nova Data - Conclusão", each fnAjustarDataConclusao([Termino]))

Is there any way to adjust this, so that it creates the dates for the following month automatically?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi amaral_diego ,

     

    Are you trying to delay the end date of a project whose due status is not yet Complete by one month?

    Please try code as below to create a calculated column.

    AjustarData = 
VAR _DATEDIFF = DATEDIFF('Table'[Termino],TODAY(),Day)
RETURN
IF('Table'[Status] = "Complete",'Table'[Termino],IF('Table'[Termino]<TODAY(),EOMONTH(TODAY(),-1)+DAY('Table'[Termino]),'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.

     

     

    • amaral_diego's avatar
      amaral_diego
      Helper II
      1 year ago

      Hi, with this code is almost helped me, it worked to send all projects to the next moth, but, for example, now I've 6 projects delayed from september that is now send to october, although I've one deleyed in october and he still in octorber, is possible to sent him do november?

      If not, it's ok, I'll acept thi one as solution, thanks a lot.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.