cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vikingw2k Frequent Visitor
Frequent Visitor

Count Distinct Dates between 2 dates, by ID

Hi peeps

 

I have this data

 

DateIDValue
1/1/20188088100
1/1/20188088100
1/1/20188088100
2/1/201880880
3/1/20188088300
3/1/20189999100
10/1/201899990
17/1/20189999200
20/1/20189999400


How can I calculate

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

b) Sum of Value where value is > 0.

 

IDStart DateEnd DateDistinct Count of Dates (value > 0)Sum of Value
80881/1/20182/2/20182600
99993/1/201820/1/20183700
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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".

1.PNG2.PNG

 

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
ThomasFoster Established Member
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 )
vikingw2k Frequent Visitor
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. 

 

=) 

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.


Grouping.png

 

Result : 

 

Result.png

 

 

Thanks

-J

 

 

Did I answer your question?
If yes, Mark my post as a solution!

Thanks.

Community Support Team
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?

1.PNG

 

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

1.PNG

 

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.
vikingw2k Frequent Visitor
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
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".

1.PNG2.PNG

 

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.
vikingw2k Frequent Visitor
Frequent Visitor

Re: Count Distinct Dates between 2 dates, by ID

Spot on, thanks a bunch mate!