Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
amaral_diego
Helper I
Helper I

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 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. Is there a way to do this in Power BI?

1 ACCEPTED SOLUTION

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.

vrzhoumsft_0-1728957114818.png

 

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.

 

View solution in original post

12 REPLIES 12
amaral_diego
Helper I
Helper I

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?

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.

vrzhoumsft_0-1728897234214.png

 

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.

 

 

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.

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.

vrzhoumsft_0-1728957114818.png

 

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.

 

It worked, thanks a lot!

lbendlin
Super User
Super User

Remember that MAX( ) can take two parameters, not just one.

Kaviraj11
Super User
Super User

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())
        )
    )

     




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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.

I'm sent to you the screenshot's of the datamodel in two parts, the "Termino" column is the date the project should be completed.
part1.pngpart2.png

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors