Create Measure: Calculates the date when 60% of ordered volume will have shipped, by product.

[Anonymous]

Hi,

I'm currently importing data that shows units on order, into Powerbi. 

I'd like to create a measure that calculates the ship date of a specific item, based on a % of total "units on order" for that item.

As an example, using the table below as my source data. I'd like to calculate, in a new table without the "account" column, the date for which at least 60% of the sum of units for each product will have shipped.

Account	Product	Units on Order	Shipped Date
Jims Hardware	Hammer	50	2024-09-13
Marks	Hammer	10	2024-11-25
J&J	Hammer	30	2024-02-25
Eagle Club	Hammer	19	2024-10-13
Jims Hardware	Screwdriver	100	2024-09-13
Eagle Club	Screwdriver	500	2024-10-10
J&J	Screwdriver	70	2024-05-10
J&J	Screwdriver	25	2024-12-15
Marks	Screwdriver	400	2024-07-15
Jims Hardware	Hammer	50	2024-12-16
Marks	Hammer	10	2024-02-17
Eagle Club	Hammer	30	2024-12-18
Marks	Hammer	19	2024-06-19
Jims Hardware	Screwdriver	100	2024-08-20
Home and Yard	Screwdriver	5	2024-12-21
Eagle Club	Screwdriver	70	2024-09-22
J&J	Screwdriver	25	2024-12-23
Eagle Club	Screwdriver	400	2024-05-24
Home and Yard	Hammer	30	2024-12-01
Marks	Hammer	19	2024-06-26
J&J	Hammer	100	2024-09-27
Eagle Club	Hammer	69	2024-12-28
Home and Yard	Screwdriver	70	2024-12-29
Home and Yard	Screwdriver	50	2024-09-30
J&J	Screwdriver	10	2024-12-31
Eagle Club	Screwdriver	30	2025-01-01
Eagle Club	Screwdriver	50	2025-01-02

Product	Units on Order	Date (60% of Units Shipped)
Hammer	436
Screwdriver	1905

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You can modify it to the following dax:

Measure =
var _table1=
ADDCOLUMNS(
    'Test_Table',
    "Sum_Group",
    SUMX(FILTER(ALL('Test_Table'),'Test_Table'[Product]=EARLIER('Test_Table'[Product])),[Units on Order])*0.6,
    "Cum",    SUMX(FILTER(ALL('Test_Table'),'Test_Table'[Product]=EARLIER('Test_Table'[Product])&&'Test_Table'[Shipped Date]<=EARLIER('Test_Table'[Shipped Date])),[Units on Order]))
var _table2=
ADDCOLUMNS(
    _table1,
    "Date",MINX(FILTER(_table1,[Product]=EARLIER([Product])&&[Cum]>=[Sum_Group]),[Shipped Date]))
return
MAXX(
    FILTER(_table2,[Product]=MAX('New Table'[Product])),[Date])

Best Regards,

Liu Yang

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 from ryan_mayu  and Kedar_Pande , please allow me to provide another insight: 
Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

New Table =
SUMMARIZE('Test_Table',[Product],"Sum",SUMX('Test_Table',[Units on Order]))

2. Create measure.

Measure =
var _table1=
ADDCOLUMNS(
    'Test_Table',
    "Sum_Group",
    SUMX(FILTER(ALL('Test_Table'),'Test_Table'[Product]=EARLIER('Test_Table'[Product])),[Units on Order])*0.6,
    "Cum",    SUMX(FILTER(ALL('Test_Table'),'Test_Table'[Product]=EARLIER('Test_Table'[Product])&&'Test_Table'[Shipped Date]<=EARLIER('Test_Table'[Shipped Date])),[Units on Order]))
var _table2=
ADDCOLUMNS(
    _table1,
    "Date1",
    CONCATENATEX(
        FILTER(
            _table1,[Product]=EARLIER([Product])&&[Cum]<=[Sum_Group]),[Shipped Date],"-"))
return
MAXX(
    FILTER(_table2,[Product]=MAX('New Table'[Product])),[Date1])

3. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hi, your result provides multiple dates per item. I'm trying to get to a singular result by item. The date result should equal the first date when 60% of TOTAL units on order each item have shipped.

[Kedar_Pande]

@Anonymous 

Create measures:

TotalUnitsOnOrder = SUM('YourTable'[Units on Order])

CumulativeShippedUnits = CALCULATE( SUM('YourTable'[Units on Order]), FILTER( 'YourTable', 'YourTable'[Shipped Date] <= MAX('YourTable'[Shipped Date]) ) )

ShipDateAt60Percent = VAR TotalUnits = [TotalUnitsOnOrder] VAR TargetUnits = TotalUnits * 0.6 RETURN CALCULATE( MIN('YourTable'[Shipped Date]), FILTER( 'YourTable', [CumulativeShippedUnits] >= TargetUnits ) )

create a new table that summarizes the results without the "Account" column:

SummaryTable = SUMMARIZE( 'YourTable', 'YourTable'[Product], "Date (60% of Units Shipped", [ShipDateAt60Percent] )

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Unfortunately this doesn not return accurate results.

[ryan_mayu]

the second table is just sum the units from the first table.

What 's the expected output based on the sample data you provided?