I would like to request support for the creation of two measures in dax regarding missing payments and payments non due in a months
I have tree tables
- Client ID (dimension)
- Payments table (orders table) (dimension)
- Calendar (dimension)Main information in Client ID
-
client ID | date_of_first_payment | scheduling_of_payment |
1 | 26/05/2017 | Monthly |
2 | 14/04/2014 | Annual |
3 | 08/06/2017 | Monthly |
4 | 26/10/2016 | Annual |
5 | 10/03/2017 | Quartely |
And in payments tables
client ID | date_order | revenue |
1 | 26/05/2017 | 50 |
1 | 26/06/2017 | 50 |
1 | 28/08/2017 | 50 |
1 | 28/09/2017 | 50 |
2 | 14/04/2014 | 300 |
2 | 14/04/2015 | 300 |
2 | 16/04/2016 | 300 |
2 | 16/05/2017 | 300 |
2 | 16/05/2018 | 300 |
3 | 08/06/2017 | 10 |
3 | 08/07/2017 | 10 |
3 | 08/08/2017 | 10 |
3 | 08/09/2017 | 10 |
3 | 08/09/2017 | 10 |
4 | 26/10/2017 | 100 |
4 | 26/10/2017 | 100 |
4 | 26/10/2017 | 100 |
5 | 10/03/2017 | 30 |
5 | 10/06/2017 | 30 |
5 | 10/12/2017 | 30 |
FullDate | CalendarYearMonth |
14/04/2017 | 2017-04 |
15/04/2017 | 2017-05 |
16/04/2017 | 2017-06 |
17/04/2017 | 2017-07 |
18/04/2017 | 2017-08 |
19/04/2017 | 2017-09 |
20/04/2017 | 2017-10 |
| | |
| | |
16/08/2018 | 2018-08 |
qualitative evidence:
Client 1 – scheduling monthly - missing payment in 2017-07
Client 2 - scheduling annual – missing payment 2017-04, but he changes months of payment, so in 2018-04 it’s not missing payment, because has changed
Client 3 - scheduling monthly – any evidence
Client 4 - scheduling annual – missing payment 2017-10
Client 5 - scheduling quartely – missing payment 2017-09
My goal is to count for each CalendarYearMonth clients with missing payment based oh their scheduling or payment not due.
Eg.
2017- 07 Client 1 is missing payment, Client 2, Client 4, Client 5 not due …
how can i write this count in dax, please?
thanks
