How to measure a stock (using transaction date AND close date)

[marko24]

Hi all,

I've an issue regarding measure calculations for this table named Tableau1_transaction (yes it's a sample, the real tables includes millions of rows) :

Transaction_ID	Transaction_date	Latest_Status	Matching_Status_date
111	01/03/2025	Match	05/03/2025 18:00
222	04/03/2025	Open
333	04/03/2025	Open
444	10/03/2025	Open
555	12/03/2025	Match	13/03/2025 15:00

My objective is to count for each day, the stock which is the number of open transactions (i.e status =open OR status = Match with a future date )


I have created 3 measures : 

Items_created_at_date =

    CALCULATE (
        COUNTA ( Tableau1_transaction[Transaction_date] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            ISONORAFTER (  'Calendar'[Date] , MAX (  'Calendar'[Date]  ), DESC )
        )
    )

Items_closed_at_date =

    CALCULATE (

        COUNTA ( Tableau1_transaction[Matching_Status_date] ),

        FILTER (

            ALLSELECTED ( 'Calendar2'),

            ISONORAFTER (  'Calendar2'[Date] , MAX (  'Calendar2'[Date]  ), DESC )

        )

    )

OpenItems_stock = [Items_created_at_date] - [Items_closed_at_date]

(I have created two calendar tables using CALENDARAUTO() and create several relationships :

Sadly, the result is not the expected one : the measure Items_closed_at_date is incorrect (but the Items_created_at_date is correct) : 
(X axis : Date from 'Calendar')

Indeed, my expectations are : 

any idea ??

regards,
Marc

[marko24]

Many thanks jgeddes !!!!

[jgeddes · Accepted solution]

Maybe something like this would work for you?

Open = 
var _vTable =
ADDCOLUMNS(
	d_date,
	"_created", COUNTX(FILTER(ALL('Table'), 'Table'[Transaction_date] <= d_date[Date]), [Transaction_date]),
	"_closed", COUNTX(FILTER(ALL('Table'), DATE(YEAR('Table'[Matching_Status_Date]),MONTH('Table'[Matching_Status_Date]), DAY('Table'[Matching_Status_Date])) <= d_date[Date]),  [Matching_Status_Date]),
	"_openItems", COUNTX(FILTER(ALL('Table'), 'Table'[Transaction_date] <= d_date[Date]), [Transaction_date]) - COUNTX(FILTER(ALL('Table'), DATE(YEAR('Table'[Matching_Status_Date]),MONTH('Table'[Matching_Status_Date]), DAY('Table'[Matching_Status_Date])) <= d_date[Date]),  [Matching_Status_Date])
)
Return
MINX(
    FILTER(_vTable, [Date] = MAX(d_date[Date])),
    [_openItems]
)

This leverages a single date table with no relationships. The variable creates a virtual table with the Open Items calculated by date. (It is also calculating created and closed as I used the same table in all three measures.)
The result would look like this...

I attached the sample pbix for you to review.
Hope this helps.