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

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

Thanks.

I think this will do what you want

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

Hi Anonymous,

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