Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
leo3690
Regular Visitor

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 6
leo3690
Regular Visitor

@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 IDDATEDATE INDATE OUTTOTAL INTOTAL OUTTYPEPEN
100109/26/201609/26/2016 21 BULLS21
100110/5/201609/26/201610/5/2016 10BULLS21
100111/03/201609/26/201611/3/2016 1BULLS21
100102/11/201709/26/20162/11/2017 10BULLS21

 

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 FilterDate InDate OutCC
 9/1/20169/30/201621
 10/1/201610/31/201611
 11/1/201611/30/201610
 12/1/201612/31/201610
 1/1/20171/30/201710
 2/1/20172/10/201710
 2/1/20172/30/20170

 

 

 


@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] )
    )
)

 

 Capture.PNG

 

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] ) )
            )
    )
)

 

Capture.PNG

 

Capture.PNG

 

Check more details in the attached pbix.

 

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?

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


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

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.