Forum Discussion

mrawat7's avatar
mrawat7
Frequent Visitor
4 years ago
Solved

Create a calculate column based on date

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...
Need Help
Tutorial Requests
  • mrawat7's avatar
    mrawat7
    4 years ago

    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's avatar
Adescrit
Icon for Impactful Individual rankImpactful Individual
4 years ago

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.