Forum Discussion
Maha1
Helper II
Helper IIRunning 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...
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.
Ashish_Mathur
Super User
Super UserHi,
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.
Maha1Ashish_Mathur Thank you! This is exactly what I wanted.
- Ashish_Mathur
You are welcome.
- Maha1
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
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.