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

Reply
Maha1
Helper II
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
Ashish_Mathur
Super User
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.

Untitled.png


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

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
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.

Untitled.png


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

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

 

 

 

 

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.

Untitled.png


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

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
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

thank you

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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