cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Count Distinct Dates between 2 dates, by ID

Hi peeps

I have this data

 Date ID Value 1/1/2018 8088 100 1/1/2018 8088 100 1/1/2018 8088 100 2/1/2018 8088 0 3/1/2018 8088 300 3/1/2018 9999 100 10/1/2018 9999 0 17/1/2018 9999 200 20/1/2018 9999 400

How can I calculate

a) distinct count of dates, within start and end date, by ID?

b) Sum of Value where value is > 0.

 ID Start Date End Date Distinct Count of Dates (value > 0) Sum of Value 8088 1/1/2018 2/2/2018 2 600 9999 3/1/2018 20/1/2018 3 700
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Count Distinct Dates between 2 dates, by ID

Hi @vikingw2k,

The date column in Table1 and enddate column in Table2 are set to "Text" in your .pbix file. Please change their data type to "Date".

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Established Member

## Re: Count Distinct Dates between 2 dates, by ID

I think this will do what you want

```Measure =
CALCULATE ( DISTINCTCOUNT ( Table1[Date] ), Table1[Value] > 0 )```
Frequent Visitor

## Re: Count Distinct Dates between 2 dates, by ID

It returned 6 for all rows.

It should be Count of Distinct Dates within Start Date and End Date, by ID.

=)

Member

## Re: Count Distinct Dates between 2 dates, by ID

You can get this data in the Edit Query itself my doing some grouping:-

1. Filter the data with 0 in the Edit Query

2. Group you data and in the Operation do the distinct count.

Result :

Thanks

-J

If yes, Mark my post as a solution!

Thanks.

Community Support Team

## Re: Count Distinct Dates between 2 dates, by ID

Hi @vikingw2k,

Based on my understanding, the "StartDate" and "EndDate" are known values in a second table (suppose it's 'Table_2'), right?

That case, please create a calculated table to get your desired output.

```Table_3 =
VAR temp =
FILTER (
CROSSJOIN (
Table_1,
SELECTCOLUMNS (
Table_2,
"ID2", Table_2[ID],
"StartDate", Table_2[Start Date],
"EndDate", Table_2[End Date]
)
),
[ID] = [ID2]
&& [Date] >= [StartDate]
&& [Date] <= [EndDate]
)
RETURN
SUMMARIZE (
temp,
[ID],
"StartDate", FIRSTNONBLANK ( Table_2[Start Date], 1 ),
"EndDate", FIRSTNONBLANK ( Table_2[End Date], 1 ),
"Distinct Count of Dates(value > 0)", CALCULATE ( DISTINCTCOUNT ( Table_1[Date] ), Table_1[Value] > 0 ),
"Sum of Value", SUM ( Table_1[Value] )
)
```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Count Distinct Dates between 2 dates, by ID

Tried but I got this error message

"DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

here's the pbix file

Community Support Team

## Re: Count Distinct Dates between 2 dates, by ID

Hi @vikingw2k,

The date column in Table1 and enddate column in Table2 are set to "Text" in your .pbix file. Please change their data type to "Date".

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Count Distinct Dates between 2 dates, by ID

Spot on, thanks a bunch mate!