Forum Discussion

Mentzer's avatar
Mentzer
Frequent Visitor
3 years ago
Solved

Counting Old Dates for a Specific Condition

Hello people,   How can I deal with the following situation? I have Table1 that has all the Groups that certain ID has gone through. The Table2 has the quantity of distinct Groups that the ID has g...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Mentzer ,

     

    Here I suggest you to try this code to create a calculated column.

    Count Group in Oldest Date = 
VAR _Oldest_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table2[ID] ) )
    )
VAR _GROUPinOldestDate =
    CALCULATE (
        SELECTEDVALUE ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Date] = _Oldest_Date
        )
    )
VAR _Count_Group =
    CALCULATE (
        COUNT ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Group] = _GROUPinOldestDate
        )
    )
RETURN
    IF ( Table2[Change Group] > 1, _Count_Group, BLANK () )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mentzer's avatar
Mentzer
Frequent Visitor
3 years ago

In the followin part, the Changed Group is not recognized:

IF ( [Changed Group] > 1, 1 )

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Mentzer ,

 

Here I suggest you to try this code to create a calculated column.

Count Group in Oldest Date = 
VAR _Oldest_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table2[ID] ) )
    )
VAR _GROUPinOldestDate =
    CALCULATE (
        SELECTEDVALUE ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Date] = _Oldest_Date
        )
    )
VAR _Count_Group =
    CALCULATE (
        COUNT ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Group] = _GROUPinOldestDate
        )
    )
RETURN
    IF ( Table2[Change Group] > 1, _Count_Group, BLANK () )

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.