Forum Discussion

bsushy's avatar
bsushy
Frequent Visitor
9 years ago
Solved

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 b...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    bsushy

     

    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,

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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]))

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

Seems like you could just do a SUM of MemberMonthCount but not enough information to really say. 

bsushy's avatar
bsushy
Frequent Visitor
9 years ago

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.