calculate new customers sales only

[GiangLe]

Hi friends,

I'm working on these 2 datasets which one stores customers information (account number, account status and account opening date), the other stores account information month by month (yearmonth, Account number and Nav (which is sales)).
the 2 datasets connect via an 1-many relationship.

I want to calculate the sales of new customers (base on account opening date) with account status A and P only.

For example, the most recent month (201703), only 3 new customers (6,7,8) but only 7 and 8 have A status so only count of these 2 accounts sales. for month 201702 would  count only sales of account 4 bc account 5's status isnt qualify.
Hope someone can enlighten me on this issue.
Thanks a lots and have a nice day

[GiangLe · Accepted solution]

Hi friend,
I managed to did it myself, quite complex Dax, hope it would have others someday
NewNav =
Var
Account = values(PBI_Acc[ACC])
Return
CALCULATE(
Sum('Monthly acc performance'[Sales]),
filter(Account,
CALCULATE(COUNTROWS(PBI_Acc),
FILTER(ALLSELECTED(PBI_Acc[Yearmonth]), PBI_Acc[Yearmonth]< MIN('Monthly acc performance'[Yearmonth]))) = 0),
(PBI_Acc[Status] = "A" || PBI_Acc[Status] = "P")
)

[s2anya]

Hi  @GiangLe,

Can you please copy paste the data set?

Regards,

Sanya