Following evolution of membership dynamically

[ylemire]

Hello, I'm facing an interesting problem that I can't resolve due to my lack of knowledge in Dax language, hope you can help me with with.

In my organisation , the customer are define by a status. The status can be Active, Suspended or Inactive, the status is drive by a combinaison of differents activites , which are log in a table with a date.

The activities are :

- New member (= Active)

- Termination (=Inactive)

- Reintegration (=Active)

- Suspension (=Suspended)

What I'm trying to do is to represent the movement of the membership dynamically over the time depending of the customer status but i'm always facing the same problem.

When I try to compare one year to an another, which have no activity, my customer isn't take into consideration in the calculation because it's have nothing to refer to.

Example :

I have a member who join the organization in 2000 , nothing happen in his file until 2010, where the account is suspended. When I do the head count of active customer, this customer appear in 2000, but not in the count of 2001 and the following year, but  he is still active and should be include in the calculation until his suspension.

Since each year is a new iteration, and the information is not push foward, I need to be able to get the lastest information on the customer file even if it's not in the same period of my analysis and then compare it with the other year.

In the end , I would like to make a chart dispaying the headcount of my active membership per year and compare the year to each other and then drill down to specific customer information (age, sex, country ...)

Thanks

[ylemire]

So far so good, I succeed to accomplish what I was trying to do , but only on a date filter level and not on a year on year comparaison level. 

Now what I need to do, is to use the result of my measure at different point of time (year) and be able to compare the result to each other . I think a virtual table could be the key, but my knowledge is very limited in this matter. This is the result I aimming for 

https://1drv.ms/u/s!Ah5EGu-Zf2CbhB4zmIwB7zbb-MaW

[Anonymous]

Hi,

I would try to use LASTDATE in the filter section of a CALCULATE.

If you share your code I could easily understand what's the issue.

Tks

[ylemire]

Hi Kogikoski, I use the following measure :

Lastest Status =

VAR Maxtest = CALCULATE(MAX('Queueitem'[Log_id]);FILTER(ALLSELECTED('Queueitem');'Queueitem'[Contact No]=MAX('Queueitem'[Contact No])))

Return

LOOKUPVALUE('Queueitem'[title];'Queueitem'[Log_id];Maxtest)

Here the link to my Pbix.file : https://1drv.ms/u/s!Ah5EGu-Zf2CbhB4zmIwB7zbb-MaW

Thanks