how to display data of previous years in years but current year data in quarters in same visual?

[swtgoyal]

Hi All,

I need to create a visual to show revenue/growth of previous and current years. I have the date table, created today/prior flags and, current year/quarter flag. 

the visual should show the data at quarter/month level, i created data hierarchy so that's possisble but I am not able to display the data at year level for prior years but current year data at quarter level. 

Below visual shows yearly data so it works untill 2023 but since 2024 is current year, how to show 2 more bars as 2024 Q1, 2024Q2 and so on..  and same way if want to do at monthly level.. 2024 Jan, 2024 Feb and so on.. 

[Anonymous · Accepted solution]

Hi @swtgoyal 

Please try this:

Here I create a set of sample:

Then add 3 calculated columns:

Year/Quartor =
VAR _currentYear =
    YEAR ( TODAY () )
VAR _Year =
    YEAR ( 'Table'[Date] )
RETURN
    IF (
        _Year <> _currentYear,
        FORMAT ( [Date], "YYYY" ),
        FORMAT ( 'Table'[Date], "YYYY-Q" )
    )

Year/Month =
VAR _currentYear =
    YEAR ( TODAY () )
VAR _Year =
    YEAR ( 'Table'[Date] )
RETURN
    IF (
        _Year <> _currentYear,
        FORMAT ( [Date], "YYYY" ),
        FORMAT ( 'Table'[Date], "YYYY-MM" )
    )

Year = YEAR('Table'[Date])

Next create 2 calculated tables:

Quartor = 
SUMMARIZE('Table','Table'[Year/Quartor],'Table'[Year])

Month = 
SUMMARIZE('Table','Table'[Year/Month],'Table'[Year])

Click the Year/Quartor and select year in sort by column, and Year/Month so on 

Create the relationship between these tables:

The result is as follow:

Best Regards

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

[swtgoyal]

Thank you so much!

I created new calculated columns as suggested into the Date Table and it worked perfectly.

Regards,
Swati