Creating dynamic slicer for column month in a table

[Anonymous]

Further building this report table.

https://community.powerbi.com/t5/Power-Query/linking-to-master-table-to-match-fields-and-summarize/m...

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.

[v-juanli-msft]

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]

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]

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]

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!

[Anonymous]

Like to add to my previous reply. 

At present the 5 coloumns are showing some random continous number 37, 38,39,40. MTD is blank as there was no shipment in april.

According to me , the problem is from "supplemental part#" in the first column below. The supplemental part here is from table "Sale order line' becase all the details which follow after that is from the same table. However, "sale order line' table and "monthly budget" is connected through the bridge table. I am unable to understand why its showing random numbers for supplemental part from "sales order line" table.

When I use the supplemental part from "Monthly Budget" table then, it shows the correct details. However, the other details goes wrong in that scenario. Please see below: