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...
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

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