Forum Discussion

Prudhviraj's avatar
Prudhviraj
Frequent Visitor
8 years ago
Solved

Dynamic calculation based on Slicer value.

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

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    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

3 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    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