Conditional Column using dates and separate table

[AndyT9]

Hello there

New to PowerBI and trying to pick it up as a go along building a dashboard that will save me time every day.

I am trying to create a new column in my main data table that is based on a date and matching it to values in a different table.

For example:

Machine usage

Users PIN	Date of use
2468	02/10/2017
2468	20/01/2018
3098	05/02/2018
2568	15/01/2018
2568	20/01/2018

Subscription history

Users PIN	Subscription	Subscription Start date	Subscription End Date
2468	Bronze	01/10/2017	25/11/2017
2468	Silver	25/11/2017	15/01/2018
2468	Gold	15/01/2018	15/01/2019
3098	Gold	20/10/2017	02/02/2018
3098	Bronze	02/02/2018	02/02/2019
2568	Silver	01/01/2018	10/01/2018
2568	Bronze	10/01/2018	10/01/2019

What I am looking to do is to add to the machine usage table the details of the subscription that was active at the time of use, eg:

Users PIN	Date of use	Subscription	Subscription Start date	Subscription End Date
2468	02/10/2017	Bronze	01/10/2017	25/11/2017
2468	20/01/2018	Gold	15/01/2018	15/01/2019
3098	05/02/2018	Bronze	02/02/2018	02/02/2019
2568	15/01/2018	Bronze	10/01/2018	10/01/2019
2568	20/01/2018	Bronze	10/01/2018	10/01/2019

I am going to carry on reading and watching training videos - but right now I am not sure where and how best to achieve this.

Many thanks in advance.

Andy

[AndyT9]

Hi @Zubair_Muhammad

That looks to do exactly what I wanted, thank you for your help. Now  I am going to spend an hour making sure I understand how this works 🙂

Thanks

Andy

[Zubair_Muhammad]

Hi @AndyT9

Try this Calculated Column....

Subscription =
CALCULATE (
    FIRSTNONBLANK ( SubscriptionHistory[Subscription], 1 ),
    FILTER (
        SubscriptionHistory,
        MachineUsage[Users PIN] = SubscriptionHistory[Users PIN]
            && MachineUsage[Date of use] >= SubscriptionHistory[Subscription Start date]
            && MachineUsage[Date of use] <= SubscriptionHistory[Subscription End Date]
    )
)

[Zubair_Muhammad · Accepted solution]

@AndyT9

You can use the same pattern as above to get the other columns you want for example

Subscription Start Date =
CALCULATE (
    FIRSTNONBLANK ( SubscriptionHistory[Subscription Start date], 1 ),
    FILTER (
        SubscriptionHistory,
        MachineUsage[Users PIN] = SubscriptionHistory[Users PIN]
            && MachineUsage[Date of use] >= SubscriptionHistory[Subscription Start date]
            && MachineUsage[Date of use] <= SubscriptionHistory[Subscription End Date]
    )
)