Power BI Dynamic Column level calculation

Hi Anonymous ,

If you mean the logic of %Diff should be (4000-6000)/4000 = -33%, we can try to use the following steps to meet your requirement:

1. create a calculated table:

Column Header Table = 
var startDate = MIN('Table'[Date])
var endDate = MAX('Table'[Date])
var t = FILTER(CALENDAR(DATE(YEAR(startDate),MONTH(startDate),1),DATE(YEAR(endDate),MONTH(endDate)+1,1)-1),DAY([Date])=1)
return 
ADDCOLUMNS(CROSSJOIN(DISTINCT('Table'[Component]),UNION(ADDCOLUMNS(t,"Month-Year",FORMAT([Date],"MMM-YYYY")),ADDCOLUMNS(t,"Month-Year","% Diff"))),"Sort Column",SWITCH([Month-Year],"% Diff",COUNTROWS(t)+1,RANKX(t,[Date],,ASC,Dense)))

2. sort "Month-Year" column by "Sort Column"

3. create a measure and use it in matrix visual:

Value Measure = 
VAR t =
    FILTER (
        ALLSELECTED ( 'Column Header Table' ),
        'Column Header Table'[Component]
            IN DISTINCT ( 'Column Header Table'[Component] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Column Header Table'[Month-Year] ),
        SWITCH (
            SELECTEDVALUE ( 'Column Header Table'[Month-Year] ),
            "% Diff",
            VAR maxMonth =
                CALCULATETABLE (
                    DISTINCT ( 'Column Header Table'[Month-Year] ),
                    FILTER (
                        t,
                        'Column Header Table'[Sort Column]
                            = CALCULATE (
                                MAX ( 'Column Header Table'[Sort Column] ),
                                FILTER ( t, 'Column Header Table'[Month-Year] <> "% Diff" )
                            )
                    )
                )
            VAR minMonth =
                CALCULATETABLE (
                    DISTINCT ( 'Column Header Table'[Month-Year] ),
                    FILTER (
                        t,
                        'Column Header Table'[Sort Column]
                            = CALCULATE ( MIN ( 'Column Header Table'[Sort Column] ), t )
                    )
                )
            VAR valueInMaxMonth =
                CALCULATE (
                    SUM ( 'Table'[Amount Received] ),
                    FILTER (
                        'Table',
                        'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
                            && 'Table'[Month_Year] IN maxMonth
                    )
                )
            VAR valueInMinMonth =
                CALCULATE (
                    SUM ( 'Table'[Amount Received] ),
                    FILTER (
                        'Table',
                        'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
                            && 'Table'[Month_Year] IN minMonth
                    )
                )
            RETURN
                FORMAT ( valueInMaxMonth / valueInMinMonth - 1, "Percent" ),
            ""
                & CALCULATE (
                    SUM ( 'Table'[Amount Received] ),
                    FILTER (
                        'Table',
                        'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
                            && 'Table'[Month_Year] IN DISTINCT ( 'Column Header Table'[Month-Year] )
                    )
                )
        ),
        CALCULATE (
            SUM ( 'Table'[Amount Received] ),
            'Table'[Component] IN DISTINCT ( 'Table'[Component] )
        )
    )

Best regards,