Display Last Due Date

[Canknucklehead]

Weird issue on my end.

I'm trying to get a simple table visual to display the last entry for a scheduled due date for a purchased product.

IE I have a list of Purchase Orders for the year. Some may be large quantities that are spread out over time. My database records all the scheduled delivery dates. I'm trying to get BI to display only the open POs and the current due date, but it either shows the previous scheduled date only or all schedule due dates, regardless of open or closed status.

Date Slicer is for the current Fiscal Year. I can add a second slicer for Current Fiscal Month. I get two different results, depending on if the Fiscal Month is active or not.

IE FY 2021 is the only active slicer:

Product X  is scheduled to arrive on March 16, April 21, May 13, October 21st.  I want only the October 21st entry to show in the table as the others have been received and are closed in the Purchase Order.

If FY 2021 and FM October are the active slicers:

Product X is scheduled to arrive on May 13th. (??)  The October 21st entry simply does not appear in the list if the FM slicer is active, unless I select All

Date Dimension has been connected to the PO Table in a couple of different ways. Originally the PO Creation date was used, and then the Scheduled Due Date. Problem persists regardless of Date connection.

I have an additional Status Column (Open or Closed) that I've tried to filter with, but it doesn't work because of the way our data is stored. The Product Line is still considered Open, just with several scheduled dates, so trying to filter out the Closed items simply doesn't filter out the other due dates as the Line is still Open.

Any suggestions on how to get this sorted? If I don't include the Scheduled Due Date in the visual, it appears to function properly. But the Purchasing Department needs to see when the next delivery is due to arrive so they can make sure that we have enough raw materials on hand to make our products.

[Canknucklehead]

Unfortunately, @v-eqin-msft , this solution doesn't work. I managed to sort out the issues and get rid of the error that I posted about yesterday, but applying the filters on the dashboard still will not provide the correct information.

For example, in our system, we have a PO open for a product. Original order quantity was 400,000+. Remaining quantity is 140,000 with 2 scheduled delivery dates - Dec 10 and Dec 23. We have received 3 shipments this month - Nov 15, 16 and 17 and these are marked CLOSED in our system.

The table listing OPEN POs still shows two of the Nov dates (15, 16) when the Month is set to Nov. If set to Oct, it shows all 3 Novemeber dates. If I select December, it shows me the delivery for the 10th, but for some reason also includes a Sept delivery! But not the Dec 23 delivery.

Any other ideas? Anyone?

[v-eqin-msft]

Hi @Canknucklehead ,

Accoring to this:

IE FY 2021 is the only active slicer:

Product X is scheduled to arrive on March 16, April 21, May 13, October 21st. I want only the October 21st entry to show in the table as the others have been received and are closed in the Purchase Order.

If FY 2021 and FM October are the active slicers:

Product X is scheduled to arrive on May 13th. (??) The October 21st entry simply does not appear in the list if the FM slicer is active, unless I select All

You need a Calendar table which will be used for slicer firstly, like this:

Calendar = ADDCOLUMNS( CALENDAR("2021/1/1","2021/12/31"),"Year",YEAR([Date]),"Month", FORMAT([Date],"mmmm"),"MonthNumber",MONTH([Date] ))

Then create a flag measure:

Measure = 
VAR _lastDate1 =
    CALCULATE (
        MAX ( 'Table'[Scheduled Due Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Vendor Name] = MAX ( 'Table'[Vendor Name] )
                && 'Table'[Part Number] = MAX ( 'Table'[Part Number] )
                && YEAR ( 'Table'[Scheduled Due Date] ) <= SELECTEDVALUE ( 'Calendar'[Year] )
        )
    )
VAR _allselectMonth =
    COUNTROWS ( ALLSELECTED ( 'Calendar'[Month] ) )
VAR _lastDate2 =
    CALCULATE (
        MAX ( 'Table'[Scheduled Due Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Vendor Name] = MAX ( 'Table'[Vendor Name] )
                && 'Table'[Part Number] = MAX ( 'Table'[Part Number] )
                && MONTH ( 'Table'[Scheduled Due Date] ) < MAX ( 'Calendar'[MonthNumber] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Calendar'[Month] ) = TRUE ()
            && _allselectMonth = 12, 1,
        ISFILTERED ( 'Calendar'[Month] ) = TRUE ()
            && _allselectMonth < 12
            && MAX ( 'Table'[Scheduled Due Date] ) = _lastDate2, 1,
        ISFILTERED ( 'Calendar'[Month] ) = FALSE ()
            && MAX ( 'Table'[Scheduled Due Date] ) = _lastDate1, 1
    )

After apply it to visual-filter pane, set as "is 1", the outputs are shown below:

1. Month slicer is inactive

2. Month slicer is active and select some months:

3. Month slicer is active and select all months:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Canknucklehead]

@v-eqin-msft 

I've tried what you've suggested, but the measure doesn't want to work - it gives the following error: 

[Canknucklehead]

I am unable to supply the pbix. I can provide a table showing the information as Power BI sees it.

This is an actual PO in our system. 14 items from one vendor. The last two items are still "O" Open as they are not scheduled to be received until next month. 

If I filter out all the "C" Closed lines, the last two lines show in the visual. However, instead of showing the November due dates, it shows the Displayed Date value (In this case February). More frustratingly, it actually lists each of the items twice, the second listing showing the one March value. That is when I have only the Year slicer active. If I select a Month to show what is open for delivery in that Month, the last two line items no longer appear in the visual unless I select Feb or Mar, as it doesn't see the Nov due date for some reason.

As you can also see, it lists all 14 products multiple times, each with a different due date that may be active on a different line. So Product 1 is listed as having a due date of Feb, Mar, Oct and Nov. I'm not certain why PowerBI pulls in the same line item multiple times with different due dates as the master table only lists the one entry.

Vendor Name	PO Number	Part Number	Scheduled Due Date	Actual Due Date	Status
Vendor X	12345	Product 1	2021-02-17	2021-02-17	C
Vendor X	12345	Product 2	2021-02-17	2021-02-17	C
Vendor X	12345	Product 3	2021-02-17	2021-02-17	C
Vendor X	12345	Product 4	2021-02-17	2021-02-17	C
Vendor X	12345	Product 5	2021-02-17	2021-02-17	C
Vendor X	12345	Product 6	2021-02-17	2021-02-17	C
Vendor X	12345	Product 7	2021-02-17	2021-03-21	C
Vendor X	12345	Product 8	2021-02-17	2021-02-17	C
Vendor X	12345	Product 9	2021-02-17	2021-02-17	C
Vendor X	12345	Product 10	2021-02-17	2021-07-13	C
Vendor X	12345	Product 11	2021-02-17	2021-10-13	C
Vendor X	12345	Product 12	2021-02-17	2021-10-18	C
Vendor X	12345	Product 13	2021-02-17	2021-11-15	O
Vendor X	12345	Product 14	2021-02-17	2021-11-18	O
Vendor X	12345	Product 1	2021-02-17
Vendor X	12345	Product 2	2021-03-03
Vendor X	12345	Product 3	2021-03-03
Vendor X	12345	Product 4	2021-03-03
Vendor X	12345	Product 5	2021-03-03
Vendor X	12345	Product 6	2021-03-03
Vendor X	12345	Product 7	2021-03-03
Vendor X	12345	Product 8	2021-03-03
Vendor X	12345	Product 9	2021-03-03
Vendor X	12345	Product 10	2021-03-03
Vendor X	12345	Product 11	2021-03-03
Vendor X	12345	Product 12	2021-03-03
Vendor X	12345	Product 13	2021-03-03
Vendor X	12345	Product 14	2021-03-03
Vendor X	12345	Product 1	2021-03-03
Vendor X	12345	Product 2	2021-03-03
Vendor X	12345	Product 3	2021-07-13
Vendor X	12345	Product 4	2021-07-13
Vendor X	12345	Product 5	2021-07-13
Vendor X	12345	Product 6	2021-07-13
Vendor X	12345	Product 7	2021-07-13
Vendor X	12345	Product 8	2021-07-13
Vendor X	12345	Product 9	2021-07-13
Vendor X	12345	Product 10	2021-07-13
Vendor X	12345	Product 11	2021-07-13
Vendor X	12345	Product 12	2021-07-13
Vendor X	12345	Product 13	2021-07-13
Vendor X	12345	Product 14	2021-07-13
Vendor X	12345	Product 1	2021-07-13
Vendor X	12345	Product 2	2021-07-13
Vendor X	12345	Product 3	2021-07-13
Vendor X	12345	Product 4	2021-10-13
Vendor X	12345	Product 5	2021-10-13
Vendor X	12345	Product 6	2021-10-13
Vendor X	12345	Product 7	2021-10-13
Vendor X	12345	Product 8	2021-10-13
Vendor X	12345	Product 9	2021-10-13
Vendor X	12345	Product 10	2021-10-13
Vendor X	12345	Product 11	2021-10-13
Vendor X	12345	Product 12	2021-10-13
Vendor X	12345	Product 13	2021-10-13
Vendor X	12345	Product 14	2021-10-13
Vendor X	12345	Product 1	2021-10-13
Vendor X	12345	Product 2	2021-10-13
Vendor X	12345	Product 3	2021-10-13
Vendor X	12345	Product 4	2021-10-13
Vendor X	12345	Product 5	2021-10-18
Vendor X	12345	Product 6	2021-10-18
Vendor X	12345	Product 7	2021-10-18
Vendor X	12345	Product 8	2021-10-18
Vendor X	12345	Product 9	2021-10-18
Vendor X	12345	Product 10	2021-10-18
Vendor X	12345	Product 11	2021-10-18
Vendor X	12345	Product 12	2021-10-18
Vendor X	12345	Product 13	2021-10-18
Vendor X	12345	Product 14	2021-10-18
Vendor X	12345	Product 1	2021-10-18
Vendor X	12345	Product 2	2021-10-18
Vendor X	12345	Product 3	2021-10-18
Vendor X	12345	Product 4	2021-10-18
Vendor X	12345	Product 5	2021-10-18
Vendor X	12345	Product 6	2021-10-21
Vendor X	12345	Product 7	2021-10-21
Vendor X	12345	Product 8	2021-10-21
Vendor X	12345	Product 9	2021-10-21
Vendor X	12345	Product 10	2021-10-21
Vendor X	12345	Product 11	2021-10-21
Vendor X	12345	Product 12	2021-10-21
Vendor X	12345	Product 13	2021-10-21
Vendor X	12345	Product 14	2021-10-21
Vendor X	12345	Product 1	2021-10-21
Vendor X	12345	Product 2	2021-10-21
Vendor X	12345	Product 3	2021-10-21
Vendor X	12345	Product 4	2021-10-21
Vendor X	12345	Product 5	2021-10-21
Vendor X	12345	Product 6	2021-10-21
Vendor X	12345	Product 7	2021-11-15
Vendor X	12345	Product 8	2021-11-15
Vendor X	12345	Product 9	2021-11-15
Vendor X	12345	Product 10	2021-11-15
Vendor X	12345	Product 11	2021-11-15
Vendor X	12345	Product 12	2021-11-15
Vendor X	12345	Product 13	2021-11-15
Vendor X	12345	Product 14	2021-11-15
Vendor X	12345	Product 1	2021-11-15
Vendor X	12345	Product 2	2021-11-15
Vendor X	12345	Product 3	2021-11-15
Vendor X	12345	Product 4	2021-11-15
Vendor X	12345	Product 5	2021-11-15
Vendor X	12345	Product 6	2021-11-15
Vendor X	12345	Product 7	2021-11-15
Vendor X	12345	Product 8	2021-11-18
Vendor X	12345	Product 9	2021-11-18
Vendor X	12345	Product 10	2021-11-18
Vendor X	12345	Product 11	2021-11-18
Vendor X	12345	Product 12	2021-11-18
Vendor X	12345	Product 13	2021-11-18
Vendor X	12345	Product 14	2021-11-18

[v-eqin-msft]

Hi @Canknucklehead 

Please provide data sample and your expected or share your pbix file after removing sensitive data to help us clarify your scenario.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.