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 a slicer.

Theese counts must respect the following rules:

  • Clompleted projects in the selected year = IF( YEAR( F_Projects[project end date] ) = (Selected year in the slicer), COUNT( F_Projects[Id_projects] ) )
  • Ongoing projects in the selected year = IF( AND( YEAR( F_Projects[project start date] ) < (Selected year in the slicer), YEAR( F_Projects[project end date] ) > (Selected year in the slicer),  COUNT( F_Projects[Id_projects] ) )
  • Projects started in the selected year = IF( AND( YEAR( F_Projects[project start date] ) = (Selected year in the slicer), YEAR( F_Projects[project end date] ) > (Selected year in the slicer),  COUNT( F_Projects[Id_projects] ) )

 

Right now, the relationship between F_Projects and Calendar is setted on F_Projects[project start date] N->1 Calendar[Date].

I don't know if this could generate errors in this type of counting..

 

I also need to create a measure or a calculated column that dynamically filters some report pages, having this rule:

Year Project to consider =
IF(
    AND(
        OR(
            OR( YEAR( F_Projects[project start date] ) = (Selected year in the slicer), YEAR( F_Projects[project end date] ) = (Selected                    year in the slicer) ), 
            AND( YEAR( F_Projects[project start date] ) < (Selected year in the slicer), YEAR( F_Projects[project end date] ) > (Selected                  year in the slicer) )
        ),
        [flag network] <> "Sì"
    ), "Year Project to consider"
)

 

Can Anyone help me solve this issue????

Thank you in advance.

 

Luca

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • 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!