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 collaborato...
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.

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.