Forum Discussion

Quietlake's avatar
Quietlake
Regular Visitor
8 years ago
Solved

Count distinct between two date with date slicer for line chart

Hi, I'm new to Power BI & DAX. I had learned a lot from posts in this community. I encounter a problem and can not find a suitable way when I search the community.   Sample Source Data Table (cal...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Quietlake,

     

    Please check out the demo in the attachment. 

    1. Create a date table. 

    Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "MonthNum", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 )
)

    2. DO NOT establish relationships.

    3. Create a measure.

    Measure =
CALCULATE (
    DISTINCTCOUNT ( count_distinct[user_id] ),
    FILTER (
        'count_distinct',
        'count_distinct'[membership_period_start] <= MIN ( 'Calendar'[Date] )
            && 'count_distinct'[membership_period_end] >= MIN ( 'Calendar'[Date] )
    )
)

    Count_distinct_between_two_date_with_date_slicer_for_line_chart

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Quietlake,

 

Please check out the demo in the attachment. 

1. Create a date table. 

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "MonthNum", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 )
)

2. DO NOT establish relationships.

3. Create a measure.

Measure =
CALCULATE (
    DISTINCTCOUNT ( count_distinct[user_id] ),
    FILTER (
        'count_distinct',
        'count_distinct'[membership_period_start] <= MIN ( 'Calendar'[Date] )
            && 'count_distinct'[membership_period_end] >= MIN ( 'Calendar'[Date] )
    )
)

Count_distinct_between_two_date_with_date_slicer_for_line_chart

 

Best Regards,

Dale

Moliems's avatar
Moliems
Frequent Visitor
4 years ago

Hi there, this has been really helpful for a similar problem I am working with.

 

In addition to above, I would like to include members in the distinct count who have a blank membership end date (they are still members). Can you advise how the Dax above could to modified?

 

Thank you