Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have gone around again and again and cannot solve this problem. I am trying to determine at a specific point in time what the status of a "Customer Number" is. For example, referencing the below table, on 1/1/2011 5835 would be "OPEN". If I move the date to 3/10/2011, it would be "CLOSED".
Customer Number | Transaction Date | Description | Number |
5835 | 9/9/2010 | OPEN | 1 |
5835 | 1/20/2011 | CLOSED | 2 |
5835 | 4/28/2011 | OPEN | 3 |
5835 | 5/26/2011 | CLOSED | 4 |
5835 | 6/24/2011 | OPEN | 5 |
5835 | 8/11/2011 | CLOSED | 6 |
5835 | 9/1/2011 | OPEN | 7 |
5835 | 9/19/2011 | CLOSED | 8 |
I have thousands of these, and ultimately want to know that on X date, the count of open is Y and the count of closed is Z for all "Customer Numbers".
I referenced this thread which is similar, but isn't getting me the results I want primarily because it's using MAX Date for all rows when I want to specify a specific point in time.
Any help is appreciated, I have people doing a bunch of work but they don't know how much they're doing or have been doing.
HI, @Anonymous
After my research, you could try this way as below:
Step1:
Create a separate date table to specify a specific point in time.
Step2:
Create the two measure as below:
count of OPEN = VAR _table = ADDCOLUMNS ( 'Table', "_lastdate", CALCULATE ( MAX ( 'Table'[Transaction Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Customer Number] ), 'Table'[Transaction Date] <= SELECTEDVALUE ( 'Date'[Date] ) ) ) ) RETURN VAR _table2 = ADDCOLUMNS ( _table, "Tag", IF ( [_lastdate] = [Transaction Date] && [Description] = "OPEN", 1, 0 ) ) RETURN SUMX ( _table2, [Tag] )
count of CLOSED = VAR _table = ADDCOLUMNS ( 'Table', "_lastdate", CALCULATE ( MAX ( 'Table'[Transaction Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Customer Number] ), 'Table'[Transaction Date] <= SELECTEDVALUE ( 'Date'[Date] ) ) ) ) RETURN VAR _table2 = ADDCOLUMNS ( _table, "Tag", IF ( [_lastdate] = [Transaction Date] && [Description] = "CLOSED", 1, 0 ) ) RETURN SUMX ( _table2, [Tag] )
Result:
Best Regards,
Lin
Sorry for the delay, I did not receive a notification.
This is very, very close.
For whatever reason, when I select the date it only shows open/closed for that date only.
I've triple checked my math and formulas, but it's not aggregating all the customer numbers that are open and closed status for the given date.
hi, @Anonymous
I have tested on my sample pbix file, it works well.
If possible, please share your simple sample pbix file with your expected output for us have a test.
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |