Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating dynamic slicer for column month in a table

Further building this report table. https://community.powerbi.com/t5/Power-Query/linking-to-master-table-to-match-fields-and-summarize/m-p/1017854#M34625   I need to create a slicer for month for ...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Anonymous 

My test table

I create two new table and create relationships

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))
supplemetal table = DISTINCT(UNION(VALUES(master[supplemental part]),VALUES(Table1[supplemental part])))

Then create measures in "table1"

selected month = FORMAT(MAX('date'[Date]),"yyyy-mm")

selected month order =
CALCULATE (
    SUM ( Table1[order] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[supplemental part]
            = MAX ( Table1[supplemental part] )
            && FORMAT (
                Table1[date],
                "yyyy-mm"
            ) = [selected month]
    )
)

month+1 =
VAR next1month =
    FORMAT (
        EDATE (
            MAX ( 'date'[Date] ),
            1
        ),
        "yyyy-mm"
    )
RETURN
    CALCULATE (
        SUM ( Table1[order] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[supplemental part]
                = MAX ( Table1[supplemental part] )
                && FORMAT (
                    Table1[date],
                    "yyyy-mm"
                ) = next1month
        )
    )

month+2 =
VAR next2month =
    FORMAT (
        EDATE (
            MAX ( 'date'[Date] ),
            2
        ),
        "yyyy-mm"
    )
RETURN
    CALCULATE (
        SUM ( Table1[order] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[supplemental part]
                = MAX ( Table1[supplemental part] )
                && FORMAT (
                    Table1[date],
                    "yyyy-mm"
                ) = next2month
        )
    )

month+3 =
VAR next1month =
    FORMAT (
        EDATE (
            MAX ( 'date'[Date] ),
            3
        ),
        "yyyy-mm"
    )
RETURN
    CALCULATE (
        SUM ( Table1[order] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[supplemental part]
                = MAX ( Table1[supplemental part] )
                && FORMAT (
                    Table1[date],
                    "yyyy-mm"
                ) = next3month
        )
    )

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for the reply. At present its not working. I am not able to link the supplemental table to the master and "monthly budget table. I think, its a small understanding which I am missing.
    My report is using multiple tables from SQL data warehouse. Here are the corresponding tables with respect to your tables and my report structure:

     

    Your Report

    My Report tables

    Master

    'Sales order line - data' and 'packing slip line measures" together forms the master table for my report. They are linked through "Sales order line key". The first table has "supplemental part". Second table has "Shippped quantity" and  "Ship date" (from a child table - packing slip line)

    Table

    "Monthly Budget". 'order' in your table is basically "order budget" in my table

    Date

    Date

    Supplemental table

    Supplemental table

     

     

     

    I get the following error message when I connect the supplemental table to the other two:

     

     

    Thanks for help!

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi Anonymous 

      I produce your scenario, please check my pbix below.

       

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

        

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        thanks for response. 

        My mistake. I forgot to mention that the order budget are unique amount budgeted for each month., so they should not be summarized. Here is the excel version of it.

         

        What changes should I make to the 5 measures under "Monthly budget" table so that they "don,t summarize".

         

         

         

         Thanks for help!