Forum Discussion

RobinH's avatar
RobinH
Helper I
6 years ago
Solved

Dynamic Age as Axis

Good afternoon

 

I'm trying this now for 3 days without a solution. Have found a few hints here in the community, but it just don't work.

I would like to have a visual with the ages of collaborators of a unit. The Age should be calculated by a year filter with the next 4 Years to chose from. Perhaps, but this would be super luxury, with a breakdown to month or quater.

I Was able to create a messure for the age by a given Date, but i can not use this messure as an axis.

In best case there is also a possibilty to hide people older than 65 (retirement age) and people who have left the unit.

 

Here you find a example of how it should look like (more or less)

 

Hope it's even possible.

Regards

Robin

 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, RobinH 

     

    Based on your description, you may create two calculated tables as follows.

    Age Table:

     

    Age Table = GENERATESERIES(0,300,1)

     

    DateSlicer:

     

    DateSlicer = CALENDAR(DATE(1950,1,1),DATE(2050,1,1))

     

     

    Then you can create a measure as below.

     

    Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DynamicAge", ROUND (
                IF (
                    ISBLANK ( 'Table'[Leaving Date] ),
                    DATEDIFF ( 'Table'[DoB], MIN ( 'DateSlicer'[Date] ), YEAR ),
                    DATEDIFF (
                        'Table'[DoB],
                        MIN ( 'Table'[Leaving Date], MIN ( 'DateSlicer'[Date] ) ),
                        YEAR
                    )
                ),
                0
            )
        ),
        [DynamicAge] IN FILTERS ( 'Age Table'[Value] )
    )
)

     

     

    You may restrict the range of year with the visual level filter.

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, RobinH 

     

    Based on your description, you may create two calculated tables as follows.

    Age Table:

     

    Age Table = GENERATESERIES(0,300,1)

     

    DateSlicer:

     

    DateSlicer = CALENDAR(DATE(1950,1,1),DATE(2050,1,1))

     

     

    Then you can create a measure as below.

     

    Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DynamicAge", ROUND (
                IF (
                    ISBLANK ( 'Table'[Leaving Date] ),
                    DATEDIFF ( 'Table'[DoB], MIN ( 'DateSlicer'[Date] ), YEAR ),
                    DATEDIFF (
                        'Table'[DoB],
                        MIN ( 'Table'[Leaving Date], MIN ( 'DateSlicer'[Date] ) ),
                        YEAR
                    )
                ),
                0
            )
        ),
        [DynamicAge] IN FILTERS ( 'Age Table'[Value] )
    )
)

     

     

    You may restrict the range of year with the visual level filter.

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RobinH's avatar
      RobinH
      Helper I
      6 years ago

      Hi Allan v-alq-msft 

       

      Thats awesome, thank you very much.

      One more question, is there a possibility to create a tooltip that shows me the name, surname and DoB?

      Because at the moment it shows me the list of all peolpe which fits in to the filters.

       

      Thank you

      Regards

      Robin