Project portfolio - project for next month and due this month

[Anonymous]

Hi, 

I need to create a project portfolio, which need to count the amount of project that due next month and due this month. 

Also another measure of project amount started next month. 

How should I do that? 

I have calendar table. 

And the data table shown below 

Row number	Start	End	Project Name
1	4/12/2022	5/6/2023	xxx
2	5/10/2022	11/11/2022	yyy
3	8/9/2022	12/12/2022	zzz

Thanks for your help

[FreemanZ · Accepted solution]

You can put the Date[End] column into the slicer and replace all the ALL to VALUES in the code, like this:

ProjectDueThisMonth :=

COUNTROWS(

    FILTER(

        VALUES(Data),

        MONTH(Data[End]) = MONTH(TODAY())

    )

)

ProjectDueNextMonth :=

COUNTROWS(

    FILTER(

       VALUES(Data),

        MONTH(Data[End]) = MONTH(TODAY())+1

            &&YEAR(Data[End])=YEAR(TODAY())

        )

    )

ProjectStartNextMonth =

COUNTROWS(

    FILTER(

       VALUES(Data),

        MONTH(Data[Start]) = MONTH(TODAY())+1

            &&YEAR(Data[Start])=YEAR(TODAY())

        )

    )

p.s. ALL ignores all filter context (e.g. from slicer selection), while VALUES takes filter context into evaluation. 

[Anonymous · Accepted solution]

Hi @FreemanZ,

I'm using the measure below, but it stll not function. Do you know where I can change?

ProjectDueThisMonth _3 =

calculate(COUNTROWS(

    FILTER(

        VALUES(Data),

        MONTH(Data[End ]) = MONTH(TODAY())

    )

),

 USERELATIONSHIP('Calendar'[Date],Data[End ]))

Thanks for your help. 

[FreemanZ]

Supposing your table named Data, try to create the measures with the code below:

 

ProjectDueThisMonth := 

COUNTROWS( 

    FILTER(

        Data,

        MONTH(Data[End]) = MONTH(TODAY())

    )

)

 

ProjectDueNextMonth := 

COUNTROWS( 

    FILTER(

        Data,

        MONTH(Data[End]) = MONTH(TODAY())+1

            &&YEAR(MONTH(Data[End]))=YEAR(TODAY())

        )

    )

 

ProjectStartNextMonth =

COUNTROWS( 

    FILTER(

        Data,

        MONTH(Data[Start]) = MONTH(TODAY())+1

            &&YEAR(MONTH(Data[Start]))=YEAR(TODAY())

        )

    )

[Anonymous]

Hi @FreemanZ,

Is this code able to use with the date filter? 

Because I try to input those measure, it show blank eventhough there should be count by 1. 

I use date slicer select the month November 2022, it suppose show me 1 end next month, 1 start next month and 1 end this month. but at the end it shown blank for me. 

Thanks for your help. 

[FreemanZ · Accepted solution]

You can put the Date[End] column into the slicer and replace all the ALL to VALUES in the code, like this:

ProjectDueThisMonth :=

COUNTROWS(

    FILTER(

        VALUES(Data),

        MONTH(Data[End]) = MONTH(TODAY())

    )

)

ProjectDueNextMonth :=

COUNTROWS(

    FILTER(

       VALUES(Data),

        MONTH(Data[End]) = MONTH(TODAY())+1

            &&YEAR(Data[End])=YEAR(TODAY())

        )

    )

ProjectStartNextMonth =

COUNTROWS(

    FILTER(

       VALUES(Data),

        MONTH(Data[Start]) = MONTH(TODAY())+1

            &&YEAR(Data[Start])=YEAR(TODAY())

        )

    )

p.s. ALL ignores all filter context (e.g. from slicer selection), while VALUES takes filter context into evaluation. 

[Anonymous]

Hi @FreemanZ , 

The new measure is work but just referring to current November 2022. I wondering if I can pick the month like December 2022 or others. 

I wondering is it be okay to use the own calendar table? By clicking June 2023, it should show 1 in the result. 

Thanks for your help. 

[FreemanZ]

Yes, you can. But you need to build the relationship with the referred column, the End column or Start column. As you have two date columns, that would be a bit tricky and you may find USERELATIONSHIP function useful. Enjoy. 

[Anonymous · Accepted solution]

Hi @FreemanZ,

I'm using the measure below, but it stll not function. Do you know where I can change?

ProjectDueThisMonth _3 =

calculate(COUNTROWS(

    FILTER(

        VALUES(Data),

        MONTH(Data[End ]) = MONTH(TODAY())

    )

),

 USERELATIONSHIP('Calendar'[Date],Data[End ]))

Thanks for your help. 

[Anonymous]

Hi @FreemanZ, 

How should I use USERELATIONSHIP this function? I have buile the relationship in the model. 

Thanks for your help. 

[FreemanZ · Accepted solution]

If the active relationship is now on End column and in case you need to refer to the Start column, you would need to add a line with USERELATIONSHIP, like this:

ProjectStartNextMonth =

CALCULATE(    

    COUNTROWS(

        FILTER(

           VALUES(Data),

           MONTH(Data[Start]) = MONTH(TODAY())+1

            &&YEAR(Data[Start]) = YEAR(TODAY())

        )

     ),

    USERELATIONSHIP(Data[Start], 'Date'[Date])

)

[Anonymous]

Hi @FreemanZ , 

After applying the measure, only the duethis month is functioning, hwever other two not working. 

Do you know why it will happen?

Thanks for your help. 

[FreemanZ]

The code is checked and should be OK now:

ProjectDueThisMonth :=

COUNTROWS(

    FILTER(

        ALL(Data),

        MONTH(Data[End]) = MONTH(TODAY())

    )

)

ProjectDueNextMonth :=

COUNTROWS(

    FILTER(

        ALL(Data),

        MONTH(Data[End]) = MONTH(TODAY())+1

            &&YEAR(Data[End])=YEAR(TODAY())

        )

    )

ProjectStartNextMonth =

COUNTROWS(

    FILTER(

        ALL(Data),

        MONTH(Data[Start]) = MONTH(TODAY())+1

            &&YEAR(Data[Start])=YEAR(TODAY())

        )

    )