Using Datesbetween and Filter function

[leo3690]

Hello all - I am new to Power BI and am trying to build a report that uses one of my filters that looks between two dates.  I have my filter built on the Dates table and want whatever is selected in that filter to see if that date is between my date in and date out that is located in my intake table.

Here is what I have so far but keep gettig an error. Error message is "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."

I sure would appreciate some help.  I have been banging my head against this all day.

CC = if(DATESBETWEEN(filter(DATES,DATES[DATE]),INTAKE[DATE IN],INTAKE[DATE OUT]),INTAKE[TOTAL IN] - INTAKE[TOTAL OUT],0)

I have two tables 

1) Intake

2) Dates

These tables have a 1 to many relationship and are joined by the column Date.

Here is what the Intake table looks like

DATE

09/26/2016

11/03/2016

02/11/2017

03/25/2017

02/13/2017

03/17/2017

03/25/2017

03/25/2017

03/25/2017

The Dates table has all dates to 2018

DATE

09/19/2016

09/20/2016

09/21/2016

09/22/2016

09/23/2016

09/24/2016

09/25/2016

09/26/2016

[leo3690]

@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

[Eric_Zhang]

---

@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.

[JasonG-BI]

Hi @Eric_Zhang

Is there any way to do this in Direct Query as in my query 

http://community.powerbi.com/t5/Desktop/Slicer-to-show-dates-between-on-stock-report/m-p/394930#M180...

Any help is much appreicated?

[leo3690]

Thank you this worked.  I truly appreciate all the help.

[Eric_Zhang]

---

@leo3690 wrote:

Thank you this worked.  I truly appreciate all the help.

---

@leo3690

Glad to hear that. If no further questions, could you accept the replies making sense to close this thread? For any question, feel free to let me know.

[Phil_Seamark]

HI @leo3690

Your Intake table only has one column in your sample data.  I take it from your formula your table has more columns,.

Also, I'm just trying to understand what you need.  Any chance you can post some more info - perhaps an expected outcome (mock up in Excel if need).