Power BI Dynamic Column level calculation

[Anonymous]

Hi, I have a base table like below which stores income of my organisation. This table contains data from 2017 onwards.

One of my BA requested report like below where he will select any two consecutive Month_Year and output should be like below.

Above output is shown when Jan-20 and Feb-20 Month_Year are selected. 

What I have done as of now is that, in the Matrix visual, I have put Month-Year field in the Column part. But how do I achieve dynamic Month level calculation ?

Can someone please help me with it @Greg_Deckler 

[v-lid-msft · Accepted solution]

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,

[amitchandak]

With date calendar, you can use totalmtd or dates mtd and change % on that

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff %=divide([MTD Sales ],[last MTD Sales]) -1

[Anonymous]

Hi @amitchandak , the given formula produces blank output on the visual. Below are my formulas. Please let me know if I have missed anything.

MTD Sales = CALCULATE(SUM('Accrual Details'[Accrual Amount]),DATESMTD(Date_Dim[Date]))

Last MTD Sales = CALCULATE(SUM('Accrual Details'[Accrual Amount]),DATESMTD(DATEADD(Date_Dim[Date], -1, MONTH)))

[amitchandak]

In case your date dimension is not stooping at the current month and you are not using a filter from date dimension. The MTD formula will run from the calendar end date.

So have date filter or use a relative date filter at page or visual level.