Forum Discussion

kdsan's avatar
kdsan
Regular Visitor
8 years ago
Solved

Count based on change in year

Good morning. 

 

PowerBI newbie here.  I have used power query before and, to a (much) lesser extent, power pivot.  Doing this for a class project but I'd also like to be able to leverage this for work.  I have a list of donations that have been made over the course of 4 years.  The most pertinent columns are ID and Year.  I'd like to be able to see which donors have made multiple donations within the same year, and which have made multiple donations over several years.  I imagine it would look something like this:

 

So far I tried the following for the donations within the same year, but it's not quite what I need. It appears to just simply doing a count of donations by ID without taking into account the change in year.

Repeat Within Same Year =
COUNTX(
FILTER(
'Donation_Level',
'Donation_Level'[id]=EARLIER(Donation_Level[id])
),
Donation_Level[Year]
)

Am I better off doing this in the Edit's Query Section (power query?) or in the data section (power pivot?)

 

Any advice would be appreciated.  

 

Thanks!

 

  • kdsan

     

    Try this calculated column

     

    Repeat same YEAR =
    VAR mycount =
        CALCULATE (
            COUNTROWS ( Donation_Level ),
            ALLEXCEPT ( Donation_Level, Donation_Level[ID], Donation_Level[Year] )
        )
    RETURN
        IF ( mycount > 1, "Y", "N" )

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion

    kdsan

     

    Try this calculated column

     

    Repeat same YEAR =
    VAR mycount =
        CALCULATE (
            COUNTROWS ( Donation_Level ),
            ALLEXCEPT ( Donation_Level, Donation_Level[ID], Donation_Level[Year] )
        )
    RETURN
        IF ( mycount > 1, "Y", "N" )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion

      kdsan

       

      And this one

       

      Repeat Multiple Years =
      VAR mycount =
          CALCULATE (
              DISTINCTCOUNT ( Donation_Level[Year] ),
              ALLEXCEPT ( Donation_Level, Donation_Level[ID] )
          )
      RETURN
          IF ( mycount > 1, "Y", "N" )