cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BooRadley
Regular Visitor

DAX Measure to count if two date conditions are met

I have been utilizing these forms for a few months now, and it has been an amazing source of help to learn how to use Power BI.

 

I am currently struggling with coming up with DAX Measure that I would use to show a count of rows that match two date requirements.  So, for the row to be counted the row needs one column value to be greater than the day the count is being done for, and then it would need to check an additional column and see if the date in that column is less than the day the count is being done for.  If both are true, it is counted.  If either or both are false, it is not counted

 

For example, using the data set below, I am trying to write an expression that will show the number of invoices open on every day of the time period.  For this set it would be January 4th through January 14th.  To do this I need to count the number of rows where the Created-on Date is less than the date of the count and that the Closure date is greater than the date of the count.

 

For example, at the end of January 6th there would be 8 invoices open

 

I'm hoping to use this to look at how the number of invoices opened changed day by day.  Thank you in advance for any help or any ideas someone may have.

 

InvoiceNumber  Invoice Created OnInvoice Closure dateMaterial description
889121/4/20221/5/2022Item 3
890251/4/20221/13/2022Item 14
887921/4/20221/14/2022Item 17
887671/4/20221/28/2022Item 4
892011/5/20221/18/2022Item 11
893411/5/20221/6/2022Item 17
894871/5/20221/31/2022Item 5
893991/5/20222/1/2022Item 11
896941/6/20221/19/2022Item 11
896891/6/20222/2/2022Item 9
900601/7/20221/25/2022Item 6
902941/7/20222/3/2022Item 3
902691/7/20222/5/2022Item 10
900791/7/20222/6/2022Item 6
903451/8/20221/30/2022Item 13
908471/10/20221/26/2022Item 1
907531/10/20222/7/2022Item 6
907821/10/20222/8/2022Item 6
912751/11/20221/15/2022Item 1
909031/11/20221/27/2022Item 6
908851/11/20222/9/2022Item 6
912721/11/20222/16/2022Item 1
916191/12/20221/20/2022Item 6
913731/12/20221/21/2022Item 11
915801/12/20222/4/2022Item 7
917591/12/20222/10/2022Item 11
913101/12/20222/12/2022Item 12
915831/12/20222/13/2022Item 8
923531/13/20221/16/2022Item 2
921701/13/20221/17/2022Item 4
922901/13/20221/22/2022Item 15
923511/13/20221/29/2022Item 2
918611/13/20222/11/2022Item 3
919931/13/20222/14/2022Item 3
922031/13/20222/15/2022Item 16
923631/14/20221/23/2022Item 17
1 ACCEPTED SOLUTION
kitgo2
Advocate I
Advocate I

Calendar table needs to have an inactive relationship in order to work:

#Active =
VAR _Active = MAX ( 'Calendar'[Date] )
RETURN
SUMX (
Sheet1,
IF (
Sheet1[Invoice Created On] <= _Active
&& ( Sheet1[Invoice Closure Date] > _Active ),
1,
0
)
)

View solution in original post

4 REPLIES 4
kitgo2
Advocate I
Advocate I

Calendar table needs to have an inactive relationship in order to work:

#Active =
VAR _Active = MAX ( 'Calendar'[Date] )
RETURN
SUMX (
Sheet1,
IF (
Sheet1[Invoice Created On] <= _Active
&& ( Sheet1[Invoice Closure Date] > _Active ),
1,
0
)
)

Thank you, the key thing I was struggling with making the date relationship as inactive from active.  For some reason, I was hung up on thinking that it was required to have the open date as a active relationship.  I very much appreciate the help you have provided and how quickly you responded.

ManguilibeKAO
Resolver I
Resolver I

Hi BooRadley,

Please, explain how, with the inout data you provided,  you count 8 open inoices at the end of January 6th.

 

Best regards.

 

So at the end of the 6th, 10 invoices have been opened but 2 have been closed. Invoice 8892 closed on the 5th and invoice 89341 closed on the 6th. 

 

I hope that clarifies what I meant. 

 

InvoiceNumber  Invoice Created OnInvoice Closure dateMaterial description
889121/4/20221/5/2022Item 3
890251/4/20221/13/2022Item 14
887921/4/20221/14/2022Item 17
887671/4/20221/28/2022Item 4
892011/5/20221/18/2022Item 11
893411/5/20221/6/2022Item 17
894871/5/20221/31/2022Item 5
893991/5/20222/1/2022Item 11
896941/6/20221/19/2022Item 11
896891/6/20222/2/2022Item 9

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.