topic Project portfolio - project for next month and due this month in DAX Commands and Tips

Project portfolio - project for next month and due this month

Anonymous — Tue, 15 Nov 2022 12:58:34 GMT

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

Re: Project portfolio - project for next month and due this month

FreemanZ — Tue, 15 Nov 2022 15:01:09 GMT

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

)

Re: Project portfolio - project for next month and due this month

Anonymous — Wed, 16 Nov 2022 09:49:43 GMT

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.

Re: Project portfolio - project for next month and due this month

FreemanZ — Wed, 16 Nov 2022 12:14:11 GMT

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

)

Re: Project portfolio - project for next month and due this month

FreemanZ — Wed, 16 Nov 2022 12:19:36 GMT

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.

Re: Project portfolio - project for next month and due this month

Anonymous — Wed, 16 Nov 2022 12:48:33 GMT

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.

Re: Project portfolio - project for next month and due this month

FreemanZ — Wed, 16 Nov 2022 13:04:32 GMT

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.

Re: Project portfolio - project for next month and due this month

Anonymous — Wed, 16 Nov 2022 13:39:12 GMT

Hi FreemanZ,

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

Thanks for your help.

Re: Project portfolio - project for next month and due this month

Anonymous — Wed, 16 Nov 2022 14:02:36 GMT

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.

Re: Project portfolio - project for next month and due this month

FreemanZ — Wed, 16 Nov 2022 14:04:21 GMT

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

)

Re: Project portfolio - project for next month and due this month

Anonymous — Wed, 16 Nov 2022 14:19:45 GMT

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.