Filtering and programming x axis.

[Anonymous]

Hello, 

I am attempting to convert my data report from Excel to Power BI,

I am trying to convert a column graph which shows the average calculation of the previous 3 years then the average calculation of each month in the current year and finally the average calculation of the current year so far. The figure below shows how it looks on excel.

My date data is currently structured in the MM/DD/YYY format, I created another column that if the date lies within the last three years it will output YE 2019, 2020 or 2021 and whenever the date lies in the current year only the month will be extracted. The problem lies in the fact I cannot extract both the month of the current year and have AVG 2022 in the same cell.

Is there way to include both the month and AVG 2022 in the x-axis?

The only thing i was able to think up was if i could define what each x-axis term would be and then program each term to do the calculations filtered to the correct dates, however, I am not even sure if that is possible.

I have been doing a lot of research with no success, I would appreciate the help.

[v-chenwuz-msft · Accepted solution]

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.

[Anonymous]

[Anonymous]

Hello,

Thank you for the help, it was extremely helpful. 

However, as I was trying to adapt your example to my situation I was not able to replicate the results.

I created the X costume table and the Table custom table, however Incorporated the Table costume table to my existing table Sheet2.

The problem is whenever I generate the stacked column chart it does not show the value for the AVG 2022 bar. 

I think the problem lies in the measure DAX created in the Table custom table.

In your example whenever you are calculating the value for AVG 2022 you do so by calculating the average of Values Column,

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])))

However in my case I need to calculate my value from an existing Measure I created called Rejection Rate, below is my modified version .

Measure =

IF(SELECTEDVALUE('X'[X-axis])="AVG "&YEAR(TODAY()),

CALCULATE('Sheet2'[Rejection Rate],FILTER(ALL('Sheet2'[Date]),YEAR([Date])=YEAR(TODAY()))),

CALCULATE('Sheet2'[Rejection Rate],USERELATIONSHIP('X'[X-axis],'Sheet2'[X-axis])))

Instead of calling out the column [Values] I called out the Measure [Rejection Rate], it is odd because this yielded correct values for all the years and months except for AVG 2022.

Thank you again for your time,

Best regards,

[v-chenwuz-msft · Accepted solution]

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.

[Anonymous]

That was it, thank you for your help. 

How did making the relationship inactive solve the problem?

[v-chenwuz-msft · Accepted solution]

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.