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

     

     

bsushy's avatar
bsushy
Frequent Visitor
8 years ago

Greg_Deckler: Yes.My data has ID and Yearmonth....I need to calculate MemberMonthCount...Thank you for the promt reply.