Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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
1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
7 REPLIES 7
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper II

@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] )))``````

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper II

@Ashish_Mathur Thank you! This is exactly what I wanted.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@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

Helper II

thank you

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors