Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
@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.
Hi @Eric_Zhang
Is there any way to do this in Direct Query as in my query
Any help is much appreicated?
Thank you this worked. I truly appreciate all the help.
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).
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
100 | |
39 | |
31 |