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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BooRadley
Frequent 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors