Show Current Year-Quarters and Past Year/Future Year wise

[archerjayden]

I want to display Year & Quarter in same axis for my Clustered Column chart however only when it is current year and for remaining past and future years It should display only year value..

Is this possible via DAX I am using DirectQuery connecting to SQL server on premise

Also have fiscal date calendar table which will have from date,week,month,quarter & year information

DataDesired Result

Thanks in Advance

Archer

[Mariusz · Accepted solution]

Hi @archerjayden 

You can add a column to your date table something like.

Column = 
IF( 
    'Calendar'[CalendarYear] = YEAR( TODAY() ),
    'Calendar'[CalendarQuarter],
    'Calendar'[CalendarYear] 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[archerjayden]

Thanks for your tip ... it worked but how do I sort this data unable to figure it out

[Mariusz]

Hi @archerjayden 

You can add a sort column 

Column = 
IF( 
    'Calendar'[CalendarYear] = YEAR( TODAY() ),
    ( 'Calendar'[CalendarYear] * 10 ) + 'Calendar'[CalendarQuarter],
    'Calendar'[CalendarYear] * 10
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[archerjayden]

Great thanks a lot for your solution 

Column = 
IF( 
    'Calendar'[CalendarYear] = YEAR( TODAY() ),
    ( 'Calendar'[CalendarYear]) + " - Q" + 'Calendar'[CalendarQuarter],
    'Calendar'[CalendarYear]
)

When I try the above to label it properly it throws an error 

"Expressions that yield variant data-type cannot be used to define calculated columns" 

How can I handle this to show 2020-Q1

[archerjayden]

Okay I have figured this out by converting the number columns to text from PowerQuery and then it did let me create calculated column .. Phew and Yay !