Forum Discussion

Maha1's avatar
Maha1
Icon for Helper II rankHelper II
3 years ago
Solved

Running count in Dax sort by date

Greetings, 

 

How can I create a colmn in DAX for running count of IDs, sorted descending by the date. For example:

 

ID date  RUNNING COUNT
111 1/1/2023  1
222 1/1/2020  1
888 1/1/2022  1
222 1/1/2018  3
222 1/1/2019  2
111 1/1/2019  2
888 1/1/2020  2
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Write this calculated column formula

    Running count = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[date]>=EARLIER(Data[date])))

    Hope this helps.

14 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Write this calculated column formula

    Running count = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[date]>=EARLIER(Data[date])))

    Hope this helps.

    • Maha1's avatar
      Maha1
      Icon for Helper II rankHelper II
      3 years ago

      Ashish_Mathur The purpose of the running count is because I want to get the company name of the previous record for each employee. Would you please help me create the calculated column to get the previous company? below is an example:

       

      ID date  RUNNING COUNT Company PREVIOUS COMPANY 
      111 1/1/2023  1  a b
      222 1/1/2020  1  c a
      888 1/1/2022  1  x y
      222 1/1/2018  3  b  
      222 1/1/2019  2  a b
      111 1/1/2019  2  b  
      888 1/1/2020  2  y  

       

      I tried this but it didn't work:

       

       

       

      Previous company = CALCULATE(   
                maxx ('Table', 'Table'[Company]), 
                filter ( 'Table', 'Table'[ID] = EARLIER('Table'[ID]) 
                && 'Table'[Running Count] >= EARLIER('Table'[Running Count] )))

       

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        You do not need a running count column for that.  Try this calculated column formula

        Previous Company = LOOKUPVALUE(Data[Company],Data[date],CALCULATE(MAX(Data[date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[date]<EARLIER(Data[date]))),Data[ID],Data[ID])

        Hope this helps.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Maha1 , a new measure

     

    countx(filter(allselected(Table), Table[ID] = max(Table[ID])  && Table[Date] >= max(Table[Date]) ) , Table[ID])

     

    You can also consider window function

    Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

  • Scubadiver007's avatar
    Scubadiver007
    Icon for Helper I rankHelper I
    2 months ago

    Hi, I have a similar query. My data has three columns (the last two is what I want to achieve)

     

    What I need to do is get the rows in yellow. This means finding out which Person IDs are duplicated, sorting the date in ascending order and exclude the initial referral. In Excel it would mean excluding the '1' in the 4th column and including the '1' in the 5th column.

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 months ago

      Hi,

      Share the download link of the Excel file with your formula already written.  I will convert that Excel formula into a PowerBI solution.

      • Scubadiver007's avatar
        Scubadiver007
        Icon for Helper I rankHelper I
        2 months ago

        Hi,

         

        I don't have a download link because I wouldn't know how to.

         

        The fourth column is: =IF(C4=C3,D3+1,1)

        The fifth column is: =IF(COUNTIF(C:C,C3)=1,0,1)

         

        I will add that this needs to be responsive to using a filter on the page. Can this be done?

         

         

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 months ago


    another variant

    count = 
VAR _ID = 'DATA'[ID]
VAR _Date = 'DATA'[date]
RETURN
COUNTAX(
    FILTER('DATA','DATA'[ID]=_ID&&[date]>=_Date),[date])