Forum Discussion
YTD Project Count not working
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?
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-fiscal-attributes-using-power-query/
Another option instead of using the DatesYTD is you could use the TOTALYTD which could also possibly give you the desired results?
5 Replies
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 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-fiscal-attributes-using-power-query/
Another option instead of using the DatesYTD is you could use the TOTALYTD which could also possibly give you the desired results?
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
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
