Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
archerjayden
Helper I
Helper I

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

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

 

DataDataDesired ResultDesired Result

Thanks in Advance

Archer

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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




View solution in original post

5 REPLIES 5
Mariusz
Community Champion
Community Champion

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




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

 

Cost Benefit PBI 2.png

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


 

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

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 !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.