Calculate specific 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. I have also tried using the "Related" function, this works with the dummy-file but unfortunately not with the original file.

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 ,

The RELATED function requires that a regular relationship exists between the current table and the table with related information.

Please try:

Exact membership_type on course date = 
VAR _date = LOOKUPVALUE('Course'[Date],'Course'[Participants],'Membership'[Name])
VAR _result = IF(_date>='Membership'[From_date]&&_date<='Membership'[To_date],'Membership'[Membership_type])
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Hi @Anonymous,
Thank you for your reply!

I will have a look at it next week and let you know if it sloved my problem 😀

[Anonymous]

Hi @Anonymous ,

Any update?🤗