Forum Discussion

AjithReddy049's avatar
AjithReddy049
Frequent Visitor
3 years ago
Solved

matrix Column headers Order Handling

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 NameFY-20FY-21FY-22Remaining Months - 2023Sep-23Oct-30Nov-23
Bikes       
Accessories       
Clothing       




 



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

 

kindly suggest this.


Thanks
Ajith





Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    AjithReddy049
    Frequent Visitor
    3 years ago

    amitchandak 


    thanks for the response.

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