matrix Column headers Order Handling

[AjithReddy049]

Hi Team,

I have some requirements like.

in a matrix visual would like to show the latest 3 months' column headers like Sep-2023, Oct-2030, and Nov-2023 in the current year,
and the remaining current year months column header "Remaining Months - 2023".

In previous years yeas data Headers like FY-22, FY-21, FY-20

Product Name	FY-20	FY-21	FY-22	Remaining Months - 2023	Sep-23	Oct-30	Nov-23
Bikes
Accessories
Clothing

Prix Link: https://1drv.ms/u/s!Aut620beS3BbgQhFFC6UAPK1wWUy?e=qU9hXq

kindly suggest this.

Thanks
Ajith

[Anonymous · Accepted solution]

Hi @AjithReddy049 ,

I created your sample pbix file(see the attachment), please check if that is what you want. Please create a calculated column as below and put it on the Columns Field of matrix just like below screenshot...

Column = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Query1'[DueDate] ), ALL ( 'Query1' ) )
VAR _mindate =
    EOMONTH ( _maxdate, -3 ) + 1
VAR _mindyear =
    YEAR ( _mindate )
VAR _dyear =
    YEAR ( Query1[DueDate] )
RETURN
    IF (
        'Query1'[DueDate] >= _mindate
            && 'Query1'[DueDate] <= _maxdate,
        'Query1'[Format],
        IF (
            _dyear = _mindyear,
            "Remaining Months - " & _mindyear,
            "FY-" & RIGHT ( _dyear, 2 )
        )
    )

Best Regards

[AjithReddy049]

@amitchandak 

thanks for the response.

Could you please elaborate above measure, [FY] is it a measure? if measure what I need to aggregate?

[amitchandak]

@AjithReddy049 , You have create a new column in date table with these header and also the sort column

Example

COlumn = Switch(True(),

[FY] < "FY-23", [FY],

Month([Date]) < 10, "Remaining month of 23",

Format([Date], "mmm-yyyy"))

Same way create an order column too

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c