YTD Project Count not working

[cnoakland]

I want to count the number of projects created for the fiscal year to date. My data has a Project ID column and a date entered column.

Here is my DAX:  

YTD Project Creation = 
CALCULATE(DISTINCTCOUNT(Detail[Project ID]),DATESYTD(Detail[Entered Date],"9/30/2014"))

Now... the count is correct. There were 8 projects created in FY17 so far. But this is my visual (this is a just an excel table- but you can get the idea of the Table visual in Power BI)

Region_1	YTD Project Creation
SOUTH	5
CENTRAL	4
NA	4
CANADA	2
EAST	1
WEST	1
TOTAL	8

All the regional numbers are incorrect. Why are the regions not adding up to the total? 

[v-jiascu-msft]

Hi @cnoakland,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @cnoakland,

Because of the context. The context of Total is the whole "Region_1". It seems you have one project in many regions. Maybe you data looks like this:

Entered Date	Region_1	Project ID
1/4/2017	Canada	         1
1/10/2017	Canada	         3
1/2/2017	Central	         1
1/8/2017	Central          3
1/5/2017	East	         1
1/11/2017	East	         4
1/3/2017	NA	         1
1/9/2017	NA	         4
1/1/2017	South	         1
1/7/2017	South	         2
1/6/2017	West	         1
1/12/2017	West	         5

Then you can try this formula:

V2 YTD Project Creation =
SUMX (
    SUMMARIZE ( 'Detail', Detail[Region_1], "YTD P C", [YTD Project Creation] ),
    [YTD P C]
)

Best Regards!

Dale

[cnoakland]

I am trying to count the number of projects created YTD for each region. My data has a column for project ID, entered date, and region. My DAX is: 

YTD Project Creation = 
			CALCULATE(DISTINCTCOUNT(Detail[Project ID]),datesytd(Detail[Entered Date],"9/30/2014"))

But here is my visual (it's just an excel table here, but it's the table visual option in PowerBI). The count is correct- there were 8 projects created so far this year, but all the other data is wrong. What am I missing? Thanks.

Region_1	YTD Project Creation
SOUTH	5
CENTRAL	4
NA	4
CANADA	2
EAST	1
WEST	1
TOTAL	8

[GilbertQ · Accepted solution]

Hi @cnoakland

What I would suggest doing to ensure that the YTD or DatesYTD is working correctly is to ensure that you have a Date table in your Power BI Model.

The DAX Time Intelligence functions rely on there being a Date table in order to function correctly.

You can create the Date table here: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

Another option instead of using the DatesYTD is you could use the TOTALYTD which could also possibly give you the desired results?

[cnoakland]

Thank you @GilbertQ. I am new to Power BI and didn't fully understand the DAX time intelligence. I created a Date table and got everything working correctly. Cheers!