Using Datesbetween and Filter function

---

leo3690 wrote:

Phil_Seamark - Thank you for taking a look at this and sorry I didn't give enough details.  Please let me know if this is enough and if not I can add more.

 

Intake Table does have more columns.  Here is an example:

 

CUSTOMER ID	DATE	DATE IN	DATE OUT	TOTAL IN	TOTAL OUT	TYPE	PEN
1001	09/26/2016	09/26/2016		21		BULLS	21
1001	10/5/2016	09/26/2016	10/5/2016		10	BULLS	21
1001	11/03/2016	09/26/2016	11/3/2016		1	BULLS	21
1001	02/11/2017	09/26/2016	2/11/2017		10	BULLS	21

 

Here is what I am trying to accomplish:  If I select a date range for Sept 2016 I want to get the current count of items for that time period.

 

Between Date Filter	Date In	Date Out	CC
	9/1/2016	9/30/2016	21
	10/1/2016	10/31/2016	11
	11/1/2016	11/30/2016	10
	12/1/2016	12/31/2016	10
	1/1/2017	1/30/2017	10
	2/1/2017	2/10/2017	10
	2/1/2017	2/30/2017	0

 

 

 

---

leo3690

Try to create calculated columns

stock = Intake[TOTAL IN]-Intake[TOTAL OUT]

currrent count =
CALCULATE (
    SUM ( Intake[stock] ),
    FILTER (
        Intake,
        EARLIER ( Intake[CUSTOMER ID] ) = Intake[CUSTOMER ID]
            && Intake[DATE] <= EARLIER ( Intake[DATE] )
    )
)

And then create a measure

CC =
CALCULATE (
    MAX ( Intake[currrent count] ),
    FILTER (
        Intake,
        Intake[DATE]
            = CALCULATE (
                LASTDATE ( Intake[DATE] ),
                FILTER ( Intake, Intake[DATE] <= MAX ( DATES[Date] ) )
            )
    )
)

Check more details in the attached pbix.