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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.