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 follwing two functionality:

  1. Provide 4 coulmns: At any point of time there needs to be a column in the table for current month +next 3 month. Ex. April..... May, June,  July    providing the amount budgeted for these months for respective supplemental part#   
  2. Provide 1 column Remaining shipment for whatever is the current month, which is budgeted quantity for current month - MTD shipment  . Budgeted quantity will be what we see under step 1 here.   Here is the excel version of the table and formulaSee Col K for #2 and Col L,M,N for #1 aboveHere is what I am trying achieve in power Bi table . I already have MTD shipment coulmn (showing 0 becase no shipment yet). I need the "Still due" column and the budget column for May, June, julyThe next three column should be next to Budget -april                           

Greg_Deckler , Can yo uplease provide your input and any further info required.

 

Thanks, in advance for help.

 

Need Help

5 Replies

  • 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.