Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
Solved! Go to Solution.
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.
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 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.
You are welcome.
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |