Filtering tables

[MelyC]

Hi,

I want to show orders new in stock.

My table strukture:

Order             stock               Date

A                    10 tons            01.06.2022

A                     10 tons           02.06.2022

A                     10 tons           03.06.2022

A                      10 tons          04.06.2022

B                       5 tons           02.06.2022

B                       5 tons           03.06.2022

B                        5 tons           04.06.2022

C                       3 tons           02.06.2022

1. I want to get a list with all new orders in stock after 01.06.2022 - I expect Order B and C

2. I want to get a list with all orders, not longer in stock at the 04.06.2022 - I expect Order C

3. I want to get a list with all orders, new in stock an also still at stock at the 04.06.2022 - I expect Order B (I dont want to see all orders booked in and also out of stock between 02.06. and 04.06. )

I get List 1 and 2 - but I dont know how to get List 3....

The dates (01.06. and 04.06.2022) should be able to selected by the user.

Can anybody help me?

Thanks

Mely

[MelyC]

Thanks for your help, but this will not work....

I need a varaible definition of the compared dates - not only "last month" or "last 12 months" - the dasboard is the basic information needed for a team-meeting. This meeting happens not in regular intervals, so we must be able to compare specific dates (for example 1. Meeting 01.06.-07.06. ---- second Meeting 07.06.-20.06.).

I can show the stock from the 20.06.2022 and the stock from 07.06.2022. I copy the table and get two tables "actuall stock" and "previous stock). Than I filter the table "actual stock" to 20.06.2022 and the table "previous stock" to 07.06.2022. I do this via slicers.

I can also show all new orders in stock between 07.-20.06.2022. I filter the table and select all orders with stock-version-date 08.06 to 20.06. and remove duplicates. Also by slicer.

I can also show orders leaving stock between 07.-20.06.2022 (filter the table and select all orders with stock-version-date 07.06. to 19.06 (but not 20.06.) and remove duplicates.

But now I have to show additionally all orders new in stock between 07.-20.06.2022 which are still in stock at the 20.06.2022

Can I get the input from the slicers as a variable option to your solutions instead of "last month" or "last 12 months"?

Thanks a lot

Mely

[v-yiruan-msft]

Hi  @MelyC ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table and apply the date field of date dimension table on the slicer

2. Create a measure as below:

Flag = 
VAR _mindate =
    MIN ( 'Calendar'[Date] )
VAR _maxdate =
    MAX ( 'Calendar'[Date] )
VAR _selorder =
    SELECTEDVALUE ( 'Table'[Order] )
VAR _minsdate =
    MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Order] = _selorder ), [Date] )
VAR _mindstock =
    CALCULATE (
        SUM ( 'Table'[stock] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Order] = _selorder
                && 'Table'[Date] = _mindate
        )
    )
VAR _maxdstock =
    CALCULATE (
        SUM ( 'Table'[stock] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Order] = _selorder
                && 'Table'[Date] = _maxdate
        )
    )
RETURN
    IF ( _mindate = _minsdate && _mindstock = _maxdstock, 1, 0 )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[MelyC]

thanks for your help but it is not the right for me.

I try to create a example...Test QIBLO.pbix

Kind regards

Mely

[v-yiruan-msft]

Hi @MelyC ,

It seems that I have no access to your shared file. Could you please grant me the proper access to the file? Or

you can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[MelyC]

I am sorry - it seems that our IT-security-settings blocked this.

Can I send it by mail?

[amitchandak]

@MelyC , To me it seems very similar to these customer Retention logics , check if these can help. You might need this date vs last day  or range VS Range

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...