Re: Using Datesbetween and Filter function

leo3690 · ‎04-27-2017

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

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 · ‎04-28-2017

@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 · ‎04-28-2017

@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 · ‎04-12-2018

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 · ‎05-03-2017

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

Eric_Zhang · ‎05-03-2017

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