Forum Discussion
Total Projects for Fiscal
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.
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.
7 Replies
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
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 + 0Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Anonymous that fixed the measure. However I am now getting this error in the card visualization.
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.
Hi,
Try this approach
- Create a Calendar Table
- Build a relationship (Many to One and Single) from the Start column to the Calendar Table
- Write this measure
Measure = calculate(countrows(projects),datedbetween(calendar[date],date(2023,11,1),date(2024,10,30)))
