topic Min Posting Date - Total qty of 3 or more in DAX Commands and Tips

Min Posting Date - Total qty of 3 or more

christina4444 — Tue, 12 Dec 2023 22:14:46 GMT

Hi Community!

Can anyone help me with a DAX formula to reflect:

If total qty is equal to or greater than 3 then show minimum posting date.

Ex:

Invoice #: Posting Date: Qty:

A 9/1/23 2

B 10/5/23 1

In this case, I want the formula to return 10/5/23 as 2 + 1 = 3 (minimum qty threshold)

Thank you!

Christina

Re: Min Posting Date - Total qty of 3 or more

Jihwan_Kim — Wed, 13 Dec 2023 04:06:50 GMT

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

WINDOW function (DAX) - DAX | Microsoft Learn

Expected result measure: = VAR _t = ADDCOLUMNS ( SUMMARIZE ( ALL ( data ), data[Group], data[Invoice_number], data[posting_date], data[Qty] ), "@runningtotalbygroup", CALCULATE ( SUM ( data[Qty] ), WINDOW ( 1, ABS, 0, REL, SUMMARIZE ( ALL ( data ), data[Group], data[Invoice_number], data[posting_date], data[Qty] ), ORDERBY ( data[posting_date], ASC ), , PARTITIONBY ( data[Group] ), MATCHBY ( data[Group], data[posting_date], data[Invoice_number] ) ) ) ) VAR _condition = FILTER ( _t, data[Group] = MAX ( data[Group] ) && [@runningtotalbygroup] >= 3 ) VAR _startdate = MINX ( _condition, data[posting_date] ) RETURN _startdate

Re: Min Posting Date - Total qty of 3 or more

christina4444 — Wed, 13 Dec 2023 15:10:49 GMT

Hi Jihwan_Kim,

Thanks so much for the response! I feel like your formula is sooo close.

I'm receiving an error message - maybe related to the details I'm including (?)

Here is the table I'm looking to add this measure to. (Currently has Earliest Posting Date) but it is not correctly showing since the result shows any posting date with a quantity of 1 or more (versus 3 or more).

Here is what I used for a formula in relation to your example:

Min Date Test = VAR _t = ADDCOLUMNS( SUMMARIZE( ALL ('VALUE ENTRY'), 'VALUE ENTRY'[Item No], 'VALUE ENTRY'[Customer], 'VALUE ENTRY'[Document No], 'VALUE ENTRY'[Posting Date], 'VALUE ENTRY'[Quantity] ), "@runningtotalbyitem", CALCULATE( SUM('VALUE ENTRY'[Quantity] ), WINDOW( 1, ABS, 0, REL, SUMMARIZE( ALL ('VALUE ENTRY' ), 'VALUE ENTRY'[Item No], 'VALUE ENTRY'[Customer], 'VALUE ENTRY'[Document No], 'VALUE ENTRY'[Posting Date], 'VALUE ENTRY'[Quantity] ), ORDERBY([Posting Date], ASC ), , PARTITIONBY('VALUE ENTRY'[Item No] ), MATCHBY('VALUE ENTRY'[Item No], 'VALUE ENTRY'[Customer], 'VALUE ENTRY'[Posting Date], 'VALUE ENTRY'[Document No] ) ) ) ) VAR _condition = FILTER( _t, 'VALUE ENTRY'[Item No] = MAX ('VALUE ENTRY'[Item No] ) && [@runningtotalbyitem] >=3 ) VAR _startdate = MINX(_condition, 'VALUE ENTRY'[Posting Date] ) RETURN _startdate

This is the error message I am receiving:

Thanks again!

Christina

Re: Min Posting Date - Total qty of 3 or more

Anonymous — Tue, 02 Jan 2024 09:14:04 GMT

Hi christina4444

Jihwan_Kim Good share!

I’m sorry to hear that you’re having trouble calculating the Min Posting Date, here I give you the other method:

Here's some dummy data

Create a measure, the cumulative sum of Qty is calculated based on the customer group.

total Qty = CALCULATE(SUM('Table'[Qty]), FILTER(ALL('Table'),'Table'[Customer] = MAX('Table'[Customer]) && [Posting Date] <= MAX([Posting Date])))

Create a measure, group according to customer and total Qty greater than or equal to 3, then return the minimum date. And here is the result:

_postingDate = MINX(FILTER(ALLSELECTED('Table'), [Customer]=MAX('Table'[Customer]) && [total Qty] >= 3),[Posting Date])

Regards,

Nono Chen

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