Total Projects for Fiscal

[Jhadur]

I am trying to create a card that shows the total number of projects for the current fiscal year. I created the following measure but I am getting an error that seems to be related to the RETURN portion of the measure and I don't understand what the issue is. I am trying to count the number of projects started within the current fiscal year. The table has a line for each month a project update report was submitted, so each project has multiple lines and I need a count of the unique project codes.

TOTAL PROJECTS FOR FY = 
  VAR __MinDate = DATE( 2023, 11, 01 )
  VAR __MaxDate = DATE( 2024, 10, 31 )
  VAR __Table = FILTER( 'Projects', [Start] >= __MinDate && 'Projects'[Start] <= __MaxDate )
  VAR __CALCULATE = (COUNTROWS('Projects'),FILTER('Projects','Projects'[Project Code])
  RETURN
  __Result +0

This is the error I am getting.

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table
2. Build a relationship (Many to One and Single) from the Start column to the Calendar Table
3. Write this measure

Measure = calculate(countrows(projects),datedbetween(calendar[date],date(2023,11,1),date(2024,10,30)))

[v-yilong-msft]

Hi @Jhadur ,

I agree with what Idrissshatila said. So I create a table as you mentioned.

The reason for your error comes from the fact that __Result is not defined. You can output to variables in your VAR, which means that if you want to use __Result, you need to define the variable in a VAR to guarantee output.

Also I noticed that the following string of DAX code also has an error.

 VAR __CALCULATE = (COUNTROWS('Projects'),FILTER('Projects','Projects'[Project Code])

I modified it and the final DAX code is as follows:

TOTAL PROJECTS FOR FY =
VAR __MinDate =
    DATE ( 2023, 11, 01 )
VAR __MaxDate =
    DATE ( 2024, 10, 31 )
VAR __Table =
    FILTER ( 'Projects', [Start] >= __MinDate && 'Projects'[Start] <= __MaxDate )
VAR __CALCULATE =
    COUNTROWS ( FILTER ( 'Projects', 'Projects'[Project Code] ) )
RETURN
    __CALCULATE + 0

Best Regards

Yilong Zhou

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

[Jhadur]

Thank you @v-yilong-msft that fixed the measure. However I am now getting this error in the card visualization. 

[v-yilong-msft · Accepted solution]

Hi @Jhadur ,

It looks like there is an error in your DAX code that is preventing the data from being loaded. The error message states that the text value “BNSPRJOP230245” could not be converted to a True/False type. This is usually because the text value is used in a logical expression.

So I think you can just calculate the value returned by _Table. Here is my modified DAX code:

TOTAL PROJECTS FOR FY =
VAR __MinDate =
    DATE ( 2023, 11, 01 )
VAR __MaxDate =
    DATE ( 2024, 10, 31 )
VAR __Table =
    FILTER ( 'Projects', [Start] >= __MinDate && 'Projects'[Start] <= __MaxDate )
RETURN
    COUNTROWS ( __Table )

Best Regards

Yilong Zhou

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

[Jhadur]

@v-yilong-msft 

I need to make a couple changes to the way this measure is calculated. I need to count the unique project codes with a Forecast Delivery Date "after today". This should give an accurate count of current active projects, as many of our projects span multiple fiscal years. The table has a line for each month a project update report was submitted, so each project has multiple lines and I need a count of the unique project codes. I appreciate your assistance.

Project Code	OP	Project Name	Start	Finish	Forecast Delivery Date	Sponsor	Report Status	Status Report Date
PRJ01	OP01	OP01 project 1	2022-03-01	2024-10-31	2024-10-31	VP Name	Final	2024-01-23
PRJ01	OP01	OP01 project 1	2022-03-01	2024-12-06	2024-12-06	VP Name	Final	2024-02-20
PRJ01	OP01	OP01 project 1	2022-03-01	2025-07-09	2025-07-09	VP Name	Final	2024-03-19
PRJ01	OP01	OP01 project 1	2022-03-01	2025-07-09	2025-07-09	VP Name	Final	2024-04-15
PRJ02	OP02	OP02 project 2	2024-05-01	2024-12-06	2024-12-06	VP Name	Final	2024-01-23
PRJ02	OP02	OP02 project 2	2024-05-01	2024-12-06	2024-12-06	VP Name	Final	2024-02-20
PRJ02	OP02	OP02 project 2	2024-05-01	2024-12-06	2024-12-06	VP Name	Final	2024-03-19
PRJ02	OP02	OP02 project 2	2024-05-01	2024-12-06	2024-12-06	VP Name	Final	2024-04-15

[Jhadur]

Thank you. This works perfectly. Appreciate the assistance.

[Idrissshatila]

Hello @Jhadur ,

regardless of the logic, you're returning a variable that is not defind.

if the last variable that returns the number is __calculate, then you should return __calculate