Forum Discussion

Maha1's avatar
Maha1
Helper 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...
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.

Maha1's avatar
Maha1
Helper 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
Super 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.