Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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/20...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    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

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

     

     

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

      Hi Anonymous,

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Spot on, thanks a bunch mate!