Forum Discussion

L_G's avatar
L_G
Helper I
2 years ago

Dinamic count based on year filter

Hi everyone,   I have a Fact_Table with two date columns: "project start date" and "project end date". I need to create 3 different count measures that dinamically change based on selected year in...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi L_G ,

 

I made some changes to your three measures.

Completed Projects in the selected year = 
CALCULATE(
    COUNT(F_Projects[Id_projects]), 
    YEAR(F_Projects[project end date]) = SELECTEDVALUE(Calendar[Year])
)

Ongoing Projects in the selected year = 
CALCULATE(
    COUNT(F_Projects[Id_projects]), 
    YEAR(F_Projects[project start date]) < SELECTEDVALUE(Calendar[Year]),
    YEAR(F_Projects[project end date]) > SELECTEDVALUE(Calendar[Year])
)

Projects Started in the selected year = 
CALCULATE(
    COUNT(F_Projects[Id_projects]), 
    YEAR(F_Projects[project start date]) = SELECTEDVALUE(Calendar[Year]),
    YEAR(F_Projects[project end date]) > SELECTEDVALUE(Calendar[Year])
)

 

It should now count correctly.

 

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!