Dynamic calculation based on Slicer value.

[Prudhviraj]

Hi All,

We have to create report where user can select Month_Name, based on the selection we have to show selected month and previous month of selected month in the report. And all of that I need to show arrow where I have less % compare to previous month, please look at the below image for clear idea.

Could you please help me out on this, would be really appreciable.

If you look at below example, user selected Feb then it's show Jan and Feb data. If user select Mar, then It has to show Feb and Mar data. 

Thanks,

Prudviraj

[v-caliao-msft · Accepted solution]

@Prudhviraj,

Currently, we cannot achieve this requirement in table. To work around this, we could achieve the similar requirement in table visual.

create a table and some columns.

Table =
FILTER (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    DAY ( [Date] ) = 1
)

MonthName =
FORMAT ( 'Table'[Date], "MMMM" )

MonthNumber =
MONTH ( 'Table'[Date] )

Create two measure in your original table.

Filter =
VAR selectmonth =
    IF (
        HASONEFILTER ( 'Table'[MonthName] ),
        MAX ( 'Table'[MonthNumber] ),
        BLANK ()
    )
VAR Previous_month = selectmonth - 1
VAR check =
    IF (
        MAX ( Table1[MonthNumber] ) = selectmonth
            || MAX ( Table1[MonthNumber] ) = Previous_month,
        1,
        0
    )
RETURN
    check

Change =
VAR selectmonth =
    IF (
        HASONEFILTER ( 'Table'[MonthName] ),
        MAX ( 'Table'[MonthNumber] ),
        BLANK ()
    )
VAR Previous_month = selectmonth - 1
VAR currenttype =
    MAX ( Table1[Type] )
VAR currentregion =
    MAX ( Table1[Region] )
VAR currentmonth =
    MAX ( Table1[MonthNumber] )
RETURN
    IF (
        MAX ( 'Table1'[MonthNumber] ) = Previous_month,
        BLANK (),
        IF (
            MAX ( Table1[Amount] )
                - LOOKUPVALUE (
                    Table1[Amount],
                    Table1[Type], currenttype,
                    Table1[Region], currentregion,
                    Table1[MonthNumber], Previous_month
                )
                > 0,
            "increase",
            "decrease"
        )
    )

User Filter measure in you visual filter.

Regards,

Charlie Liao

[Prudhviraj]

Thank you Charlie.

[Ashish_Mathur]

Hi,

This can be done in MS Excel using PowerPivot and CUBE functions.