Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
96 | |
87 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |