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

     

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
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,

 

 

  • bsushy's avatar
    bsushy
    Frequent Visitor
    9 years ago

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