Forum Discussion
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 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
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] )
4 Replies
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.
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
