Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 On | Invoice Closure date | Material description |
88912 | 1/4/2022 | 1/5/2022 | Item 3 |
89025 | 1/4/2022 | 1/13/2022 | Item 14 |
88792 | 1/4/2022 | 1/14/2022 | Item 17 |
88767 | 1/4/2022 | 1/28/2022 | Item 4 |
89201 | 1/5/2022 | 1/18/2022 | Item 11 |
89341 | 1/5/2022 | 1/6/2022 | Item 17 |
89487 | 1/5/2022 | 1/31/2022 | Item 5 |
89399 | 1/5/2022 | 2/1/2022 | Item 11 |
89694 | 1/6/2022 | 1/19/2022 | Item 11 |
89689 | 1/6/2022 | 2/2/2022 | Item 9 |
90060 | 1/7/2022 | 1/25/2022 | Item 6 |
90294 | 1/7/2022 | 2/3/2022 | Item 3 |
90269 | 1/7/2022 | 2/5/2022 | Item 10 |
90079 | 1/7/2022 | 2/6/2022 | Item 6 |
90345 | 1/8/2022 | 1/30/2022 | Item 13 |
90847 | 1/10/2022 | 1/26/2022 | Item 1 |
90753 | 1/10/2022 | 2/7/2022 | Item 6 |
90782 | 1/10/2022 | 2/8/2022 | Item 6 |
91275 | 1/11/2022 | 1/15/2022 | Item 1 |
90903 | 1/11/2022 | 1/27/2022 | Item 6 |
90885 | 1/11/2022 | 2/9/2022 | Item 6 |
91272 | 1/11/2022 | 2/16/2022 | Item 1 |
91619 | 1/12/2022 | 1/20/2022 | Item 6 |
91373 | 1/12/2022 | 1/21/2022 | Item 11 |
91580 | 1/12/2022 | 2/4/2022 | Item 7 |
91759 | 1/12/2022 | 2/10/2022 | Item 11 |
91310 | 1/12/2022 | 2/12/2022 | Item 12 |
91583 | 1/12/2022 | 2/13/2022 | Item 8 |
92353 | 1/13/2022 | 1/16/2022 | Item 2 |
92170 | 1/13/2022 | 1/17/2022 | Item 4 |
92290 | 1/13/2022 | 1/22/2022 | Item 15 |
92351 | 1/13/2022 | 1/29/2022 | Item 2 |
91861 | 1/13/2022 | 2/11/2022 | Item 3 |
91993 | 1/13/2022 | 2/14/2022 | Item 3 |
92203 | 1/13/2022 | 2/15/2022 | Item 16 |
92363 | 1/14/2022 | 1/23/2022 | Item 17 |
Solved! Go to Solution.
Calendar table needs to have an inactive relationship in order to work:
Calendar table needs to have an inactive relationship in order to work:
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.
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 On | Invoice Closure date | Material description |
88912 | 1/4/2022 | 1/5/2022 | Item 3 |
89025 | 1/4/2022 | 1/13/2022 | Item 14 |
88792 | 1/4/2022 | 1/14/2022 | Item 17 |
88767 | 1/4/2022 | 1/28/2022 | Item 4 |
89201 | 1/5/2022 | 1/18/2022 | Item 11 |
89341 | 1/5/2022 | 1/6/2022 | Item 17 |
89487 | 1/5/2022 | 1/31/2022 | Item 5 |
89399 | 1/5/2022 | 2/1/2022 | Item 11 |
89694 | 1/6/2022 | 1/19/2022 | Item 11 |
89689 | 1/6/2022 | 2/2/2022 | Item 9 |
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |