Exact membership status on specific date

[Anonymous]

Hi all,

I have created a dummy-file to make it easier to understand and help with the question.

A bit in general about the data: We have data on completed events, the date on which these events were carried out and which participant from the membership register has taken part. We also have data that gives us information about the members. These members have different statuses, such as ordinary, silver, gold and Vip.

I got some help earlier to create a formula that gives me the exact membership status on the specific time the course happened. But there is a problem, members who is not longer active turns out as blanks when using the formula. In the dummy-file you can see that the member Cristiano Sui`s membership status turns out as a blank when I want the exact membership status at the time of the course. In this example he would have been a gold member at the time of the excel course.

Here is the link to the dummy-file: https://www.dropbox.com/s/d375yibfsz3o77g/Dummy-file%203%2C0.pbix?dl=0

Hopefully this was understandable

Let me know if there is something i could do to help with the soultion😊

[Anonymous]

Hi @Anonymous ,

According to your description, you want to find exact membership status on specific date. Here are my steps you can follow as a solution.

(1)This is my test data.

Table Course

Table Membership

(2)We can create  a calculated columns.

Exact membership_type on course date =

CALCULATE (

    SELECTEDVALUE ( 'Membership'[Membership_type] ),

    'Membership'[From_date] <= RELATED( Course[Date] ),

    'Membership'[To_date] >= RELATED ( Course[Date] )

)

  (3)Then the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @Anonymous,

Thank you for youre contribution 😊

Unfortunatly the solution did not work on the master file.
It seems like its not able to find the relationship between the two dates...

Best regards

Andreas Å

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check that the relationship between the two tables is established as shown in the following figure.

If your problem is still not resolved after the setup, can you provide me with some special data and details of the error and an example of the output of the results you expect, thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thank you for your respons @HotChilli  🙂

I think @tamerj1 helped me out with the measurment, maybe he have a solution? 

He used it as an measure and not as a calculated colum, but i changed it to a calculated column becuase i want to be able to visualaize it (which i were not able to do with the measure) .

[tamerj1]

@Anonymous 
What exactly do you mean by "Visualize it"?

[Anonymous]

Sorry for not beeing more specific.

By visualize i mean beeing able to see number of particpants in one month in a bar chart for example. I were not able to do that when usning the measurement,  thats why i put the forumla in to a collum.

But my main problem now is the blanks as shown in the dummy-file 🙂

[HotChilli]

I think the formula you have is for a measure not a calculated column.

As a calculated column it would be redundant since you already have membership_type column (if I understand the data correctly)

---

On a side issue, I have a feeling this model won't work if the data becomes more complex, for example multiple course entries for the same person in the dimension table.  I haven't investigated further but keep an eye on it.