Forum Discussion
Filter and sum
I would like to create a calculated column "MemberMonthCount for each ID" based on "ID" and "YEARMONTH"
My aim is to find the sum of those member months for a date range.
Memeber months should be calculated only for those members which belong to that particular roster month which is selected using a slicer. Also the YearMonth should lie in the date range selected in another slicer.
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]))
10 Replies
Seems like you could just do a SUM of MemberMonthCount but not enough information to really say.
Could you help me with the calculated column "MemberMonthCount".
How do I write it....It should also be calculated only for those ID's which are in that selected memeber month '201704'
..Then it would be easy for me to write a measure for the sum.
Greg_DecklerCould you help me with the calculated column "MemberMonthCount".
How do I write it....It should also be calculated only for those ID's which are in that selected memeber month '201704'
..Then it would be easy for me to write a measure for the sum.
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
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,
By doing this i would just get the distinct ID's in each membermonth. But i would like to calculate in how many membermonths those Id's (from selected membermonth '201704') have appeared in the selected range ('201705-201605')
