Forum Discussion
Using Datesbetween and Filter function
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 |
6 Replies
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).
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 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 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.
Thank you this worked. I truly appreciate all the help.
