Filtering and programming x axis.

Hi Anonymous,

To achieve the custom X-axis, you a custom table should be created. Before this, create a column to mark the date firstly.

X-axis = 
var _currentY = YEAR(TODAY())
VAR _previoesY = _currentY-3
return
IF(YEAR([Date])=_currentY,FORMAT([Date],"mmm"),IF(YEAR([Date])>=_previoesY,FORMAT([Date],"\YE yyyy"),"Old data"))

X =
VAR _A =
    ADDCOLUMNS (
        VALUES ( 'Table'[X-axis] ),
        "MAXDate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( 'Table', [X-axis] = EARLIER ( [X-axis] ) )
            )
    )
RETURN
    UNION (
        ADDCOLUMNS ( _A, "RANK", RANKX ( _A, [MAXDate],, ASC ) ),
        { ( "AVG " & YEAR ( TODAY () ), MAX ( 'Table'[Date] ), 16 ) }
    )

X table looks like this:

Second, create a inactive relationship between fact table and X table depends on x-axis.

Finally, measure to calculate the average.

Measure =
IF (
    SELECTEDVALUE ( 'X'[X-axis] )
        = "AVG " & YEAR ( TODAY () ),
    CALCULATE (
        AVERAGE ( 'Table'[Values] ),
        FILTER ( ALL ( 'Table'[Date] ), YEAR ( [Date] ) = YEAR ( TODAY () ) )
    ),
    CALCULATE (
        AVERAGE ( 'Table'[Values] ),
        USERELATIONSHIP ( 'X'[X-axis], 'Table'[X-axis] )
    )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

Hi Anonymous ,

I forgot to remain you the relationship should be inactive.

Best Regards

Community Support Team _ chenwu zhu

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