topic Re: Show Current Year-Quarters and Past Year/Future Year wise in DAX Commands and Tips

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

archerjayden — Wed, 08 Jul 2020 07:18:09 GMT

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

Thanks in Advance

Archer

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

Mariusz — Wed, 08 Jul 2020 08:04:22 GMT

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

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

archerjayden — Wed, 08 Jul 2020 11:51:39 GMT

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

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

Mariusz — Wed, 08 Jul 2020 12:06:48 GMT

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

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

archerjayden — Wed, 08 Jul 2020 13:07:18 GMT

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

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

archerjayden — Wed, 08 Jul 2020 18:17:54 GMT

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 !