Previous month value sum based on current month criteria

[Anonymous]

Hi!

I got problem with previous month value count based on current month criteria. I need to count how many of these month ACTIVE clients, was marked as N (New clients) in previous month.

Formulas what I used:

New Clients = calculate([Count OF new];PREVIOUSMONTH(COD_DW_MART_AGENT_SCORECARD[FIRST_DAY_OF_MONTH]))

Count OF new = CALCULATE(DISTINCTCOUNT(Client_database[Client_ID]);Client_database[CLASSIFICATION2]=N)

Problem with these formulas are, that DAX counts not only active clients in these month, but inactive too who was marked as N In previous month. (For example: in previous month there was 12 clients who was market as N, but in these month there has left only 11 of them( I need that DAX calculates only these 11)( Active client means, that on current month client has new record row)

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please create measures:

previous status =
CALCULATE (
    MAX ( 'ClientStatus Table'[CLASSIFICATION2] ),
    FILTER (
        ALLSELECTED ( 'ClientStatus Table' ),
        'ClientStatus Table'[Client_ID] = MAX ( 'ClientStatus Table'[Client_ID] )
            && 'ClientStatus Table'[FIRST_DAY_OF_MONTH].[MonthNo]
                = MONTH ( MAX ( 'ClientStatus Table'[FIRST_DAY_OF_MONTH] ) ) - 1
    )
)

Count OF new2 =
CALCULATE (
    DISTINCTCOUNT ( 'ClientStatus Table'[Client_ID] ),
    FILTER (
        ALLSELECTED ( 'ClientStatus Table' ),
        [previous status] = "N"
            && 'ClientStatus Table'[FIRST_DAY_OF_MONTH]
                = SELECTEDVALUE ( 'ClientStatus Table'[FIRST_DAY_OF_MONTH] )
    )
)

Best regards,

Yuliana Gu

[AlB]

@Anonymous

Can you explain a bit more what an active client is? An example based on your sample data would help

[Anonymous]

@AlB

Data in table is documented on monthly bases. If client in current month has made any purchases than new table row has been created including exact montlhy period information( Year & Month, FIRST_DAY_OF_MONTH) , Client ID, Clasification2 and other column data ( purchase amount etc.(not related to the task))

If in current month client has made purchases and that means that he has current month row, that means clients is ACTIVE in current month. 

If it doesnt have exact month row information it means is wasnt active ( for example, Client 10, in October was active, but not in November)

Each client has been clasiffied as "NEW" or "Old" client. Client has statuss "NEW" first 6 months from first purchase ( from first time when client row appeared) after than client clasiffication is changed to "OLD" ( example, client 6, In october was classified as "NEW" But in November it has changed statuss to "OLD")

[AlB]

@Anonymous

so an inactive client in a specific month won't have a row? That is the case for client 10 in November that you provide as example but you talk about  "If it doesnt have exact month row information it means is wasnt active" What does that mean exactly? Are there cases where an inactive client in a month WILL have a row in the data?  

[Anonymous]

@AlB

yes, inactive client in a specific month wont have a row.

As about question:"Are there cases where an inactive client in a month WILL have a row in the data?" No, its not possible.

Result what I recieve right now :

 

As you see only 11 clients in november has been marked as "New clients"  in previous period, but in total is 12 (because there was 12 clients in previous period with statuss "New")

Client 10 isnt in list, because he was inactive in november

[AlB]

Hi @Anonymous

Can you share your pbix, with dummy data if necessary? Or at least show the structure of your tables (in table/text format so that they can be readily copied)

[Anonymous]

Hi @AlB

Data structure looks like that:

Client_ID	Year & Month	FIRST_DAY_OF_MONTH	CLASSIFICATION2
1	2018-10	2018.10.01. 0:00:00	O
2	2018-10	2018.10.01. 0:00:00	N
3	2018-10	2018.10.01. 0:00:00	N
4	2018-10	2018.10.01. 0:00:00	N
5	2018-10	2018.10.01. 0:00:00	N
6	2018-10	2018.10.01. 0:00:00	N
7	2018-10	2018.10.01. 0:00:00	N
8	2018-10	2018.10.01. 0:00:00	N
9	2018-10	2018.10.01. 0:00:00	N
10	2018-10	2018.10.01. 0:00:00	N
11	2018-10	2018.10.01. 0:00:00	N
12	2018-10	2018.10.01. 0:00:00	N
13	2018-10	2018.10.01. 0:00:00	N
1	2018-11	2018.11.01. 0:00:00	O
6	2018-11	2018.11.01. 0:00:00	O
8	2018-11	2018.11.01. 0:00:00	N
4	2018-11	2018.11.01. 0:00:00	N
3	2018-11	2018.11.01. 0:00:00	N
2	2018-11	2018.11.01. 0:00:00	N
5	2018-11	2018.11.01. 0:00:00	N
7	2018-11	2018.11.01. 0:00:00	N
9	2018-11	2018.11.01. 0:00:00	N
11	2018-11	2018.11.01. 0:00:00	N
13	2018-11	2018.11.01. 0:00:00	N
12	2018-11	2018.11.01. 0:00:00	N

Model consist only from one table

[Anonymous]

HI AIB,

PBIX data is in one table, and its structure is similar like that:

Client_ID	Year & Month	FIRST_DAY_OF_MONTH	CLASSIFICATION2
1	2018-10	2018.10.01. 0:00:00	O
2	2018-10	2018.10.01. 0:00:00	N
3	2018-10	2018.10.01. 0:00:00	N
4	2018-10	2018.10.01. 0:00:00	N
5	2018-10	2018.10.01. 0:00:00	N
6	2018-10	2018.10.01. 0:00:00	N
7	2018-10	2018.10.01. 0:00:00	N
8	2018-10	2018.10.01. 0:00:00	N
9	2018-10	2018.10.01. 0:00:00	N
10	2018-10	2018.10.01. 0:00:00	N
11	2018-10	2018.10.01. 0:00:00	N
12	2018-10	2018.10.01. 0:00:00	N
13	2018-10	2018.10.01. 0:00:00	N
1	2018-11	2018.11.01. 0:00:00	O
6	2018-11	2018.11.01. 0:00:00	O
8	2018-11	2018.11.01. 0:00:00	N
4	2018-11	2018.11.01. 0:00:00	N
3	2018-11	2018.11.01. 0:00:00	N
2	2018-11	2018.11.01. 0:00:00	N
5	2018-11	2018.11.01. 0:00:00	N
7	2018-11	2018.11.01. 0:00:00	N
9	2018-11	2018.11.01. 0:00:00	N
11	2018-11	2018.11.01. 0:00:00	N
13	2018-11	2018.11.01. 0:00:00	N
12	2018-11	2018.11.01. 0:00:00	N