Forum Discussion

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

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

Best Regards,

Dale

Count distinct between two date with date slicer for line chart.pbix78 KB

v-jiascu-msft

Microsoft Employee

8 years ago

Hi Quietlake,

You said you want a line chart in your first post. Regarding to the question here, the solution could be like below. Because there isn't a start date of 2018/6/1. Please refer to the snapshot.

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