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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

Mentzer check this video on my YT channel and tweak it as you see fit How to get value of each product based on the most recent transaction - Power BI - YouTube

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

 

 

  • Mentzer's avatar
    Mentzer
    Frequent Visitor
    3 years ago

    Your solution is very good, sir. Thank you very much for the video.

    But when performing the first formula with TOPN, it gives a memory error and it is not possible to perform the calculations. Is there any other solution?