Forum Discussion
Latest value before a selected date
I'm having trouble getting this to work in a DAX measure.
User picks a single date
For each CustomerID find their latest permissiontype that is before that time.
This should be in an intermediate calculated table inside the measure.
Then the output should be the distinctcount of CustomerID for each PermissionType
Sample Table (change-log):
CustomerID | Date | PermissionType
1 | 2022-04-13 | Resubscribed
1 | 2022-05-01 | Swapped
1 | 2022-06-11 | Unsubscribed
2 | 2022-05-13 | Unchanged
2 | 2022-06-14 | Swapped
3 | 2022-01-03 | New
3 | 2022-07-05 | Unsubscribed
3 | 2022-11-13 | Resubscribed
4 | 2022-07-01 | New
4 | 2022-07-05 | Swapped
4 | 2022-07-08 | Unsubscribed
5 | 2022-03-11 | Resubscribed
6 | 2022-04-10 | New
6 | 2022-05-13 | Unchanged
6 | 2022-06-02 | Unchanged
7 | 2022-05-01 | New
7 | 2022-05-17 | Unsubscribed
7 | 2022-06-15 | Resubscribed
7 | 2022-08-01 | Unsubscribed
Example 1:
Picked date : 2022-07-03
Expected Intermediate Table (should not be visible on canvas, but a calculatetable in dax)
CustomerID | PermissionType
1 | Unsubscribed
2 | Swapped
3 | New
4 | New
5 | Resubscribed
6 | Unchanged
7 | Resubscribed
Expected output on canvas:
PermissionType | Measure
New | 2
Swapped | 1
Unsubscribed | 1
Resubscribed | 1
Unchanged | 1
Example 2:
Picked date : 2022-05-14
Expected Intermediate Table
CustomerID | PermissionType
1 | Swapped
2 | Unchanged
3 | New
4 | (blank)
5 | Resubscribed
6 | Unchanged
7 | New
Output:
PermissionType | Measure
New | 2
Swapped | 1
Unsubscribed | 0 (blank?)
Resubscribed | 1
Unchanged | 2
Remember a CustomerID can have multiple entries in the change-log table so simply
DISTINCTCOUNT(CustomerID) wont work.
I only want to see the latest entry per customer that is before the selected date
8 Replies
Works like a charm, but only when the Calendar Table and the Permission Table aren't linked with a relationship.
I've tried adding some removefilters functions, but couldn't quite get it to work, appears to only give me the MAX value for a single CustomerID, i presume number 7.
Any ideas?
yes, it will work if there is no connection
or need to add two calendars
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiWuJZByhXXf8ZqfE?e=R48eEb
Yeah but that is only the intermediate table, not the one I actually want to be the output.
I want the output to be the distinctcount of customerid per permissiontype of that table you just created.So I would need the above measure to be an calculatetable in the DAX measure and then some final measure function that can calculate the number of customerIDs per type.
The final output on the canvas would not contain the CustomerID
