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...
  • v-caliao-msft's avatar
    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