Create a calculate column based on date

[mrawat7]

Hello,

I am trying to create a calculated column based on a user selected date.

I have a table containing membership data.  The table includes a column for StartDate (start of membership) and another for ExpiryDate (end of membership).

So if the user selects a date (e.g. 01/08/2022) the column would return '1' if the selected date is between StartDate and ExpiryDate, or '0' if it is not.

In SQL it would look like this:

CASE 

 WHEN @EndDate
   BETWEEN StartDate AND ExpiryDate THEN 

     1

   ELSE

     0
END

Can this be done in PowerQuery or DAX

Thanks in avanced!

M

[mrawat7]

Thanks @Adescrit,

That was really helpful and appreciated!

It works to a certain degree with my dataset.  

The Membership table has a many-to-one relationship with the Customer table, as there are scenarios where customer can have more than one membership, and I want to eliminate the possiblity of double counting Members.

Instead of the conditional statement returning 1, would it be possible to return CustomerID then Distinctcount CustomerID?

Kind regards,

M

[mrawat7 · Accepted solution]

I think I have figured it out:

Measure = 
VAR __SelectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
         distinctcount(memberships[contactid]), memberships[startdate].[Date] <= __SelectedDate, __SelectedDate <= memberships[expirydate].[Date]
    )

[Adescrit]

Excellent! Yes that looks good.

[Adescrit]

Hi @mrawat7 ,

As far as I know it is not possible to pass a selected slicer value to a calculated column.

You could use a measure though. Are you trying to calculate the number of members active on the selected date? If so you could use a formula like this:

Between =
VAR __SelectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUMX (
            Members,
            IF ( Members[StartDate] <= __SelectedDate && __SelectedDate <= Members[ExpiryDate], 1, 0 )
        ),
        ALL ( 'Date'[Date] )
    )

In this scenario we have a Date dimension table containing a list of dates, and Members is the table containing membership data.