cancel
Showing results 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
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
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
Helper II

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors