Forum Discussion
Filter and sum
In this scenario, what you want to return is just a DISTINCTCOUNT() of members within the YEARMONTH range your selected. You can just create a measure like below:
Distinct Members = CALCULATE ( DISTINCTCOUNT ( Table[ID] ), ALLSELECTED ( Table[YEARMONTH] ) )
Regards,
In your query, right-click your YEARMONTH column and duplicate it. Then, use YEARMONTH as your list slicer and YEARMONTH - Copy as your date range slicer. Then you can create a measure like this:
Measure 5 = CALCULATE(DISTINCTCOUNT('Members'[YEARMONTH]),ALLEXCEPT('Members','Members'[YEARMONTH - Copy]))
Is your raw data just like in the image where you have ID and YEARMONTH columns in your data table? Please confirm and if not, please post some sample raw data.
Please read: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Greg_Deckler: Yes.My data has ID and Yearmonth....I need to calculate MemberMonthCount...Thank you for the promt reply.
OK, to clarify one last point, I would assume that this is a subset of the data and that the members would have a continuous membership up until the time selected in the YEARMONTH slicer (201704 in your example). So, if I am following what you are trying to do, you are trying to choose a particular YEARMONTH and for members for that particular YEARMONTH, you are trying to see how long they have been a member for the date range selected and get a sum of that value. Is that all correct?
Members do not have a continuous membership up until the time selected in the YEARMONTH slicer (201704 in the example).
They can be in one YEARMONTH and not be in another YEARMONTH in the date range.
So, if I am following what you are trying to do, you are trying to choose a particular YEARMONTH and for members for that particular YEARMONTH, you are trying to see how long they have been a member for the date range selected and get a sum of that value. Is that all correct?---This is exactly what I need.
In your query, right-click your YEARMONTH column and duplicate it. Then, use YEARMONTH as your list slicer and YEARMONTH - Copy as your date range slicer. Then you can create a measure like this:
Measure 5 = CALCULATE(DISTINCTCOUNT('Members'[YEARMONTH]),ALLEXCEPT('Members','Members'[YEARMONTH - Copy]))