Latest value before a selected date

[AlexaderMilland]

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

[Ahmedx · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiWuJZByhXXf8ZqfE?e=wqGnwL

[AlexaderMilland]

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?

[Ahmedx]

yes, it will work if there is no connection

or need to add two calendars

[AlexaderMilland]

Yeah I know, but I do want the connection active for my other visuals / calculations. 
You think its possible to alter the measures to allow so without needing a second date table?

[Ahmedx]

or you can create an inactive relationship between them and activate it with USERELATIONSHIP where you need it

[Ahmedx]

you won't succeed

[Ahmedx]

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

[AlexaderMilland]

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