Forum Discussion
Helper INeed to calculate Running Count
Need to count frequency of Occurrence before certain date
Example,
Input
Name Date
Test1 29-01-2022
Test2 30-01-2022
Test3 30-01-2022
Test1 30-01-2022
Test1 31-01-2022
Test4 01-02-2022
Test3 01-02-2022
Test5 02-02-2022
output
Name Date count Frequency (Name)
Test1 29-01-2022 1
Test2 30-01-2022 1
Test3 30-01-2022 1
Test1 30-01-2022 2
Test1 31-01-2022 3
Test4 01-02-2022 1
Test3 01-02-2022 2
Test5 02-02-2022 1
Thanks in Advance
@ use this for column
Column = VAR __d = 'Table (2)'[Date] RETURN CALCULATE ( COUNTROWS ('Table (2)' ) , ALLEXCEPT ( 'Table (2)','Table (2)'[Name] ),'Table (2)'[Date] <= __d )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
17 Replies
- parry2k
Super User
@ add the following measure:
RT = CALCULATE ( COUNTROWS ('Table (2)' ), FILTER ( ALL ('Table (2)' ), 'Table (2)'[Date] <= MAX ('Table (2)'[Date] ) ), VALUES ('Table (2)'[Name] ) )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
ashaykohadTried this, but not giving required output
- parry2k
@ what you are getting? Can you provide more details? Your reply is not helpful?
- ashaykohad
I am getting total number of rows with this measure.
Let me explain the case
Test 1 used coupon on 29-01-2021
Test 2 used coupon on 30-01-2021
Test 1 used coupon on 31-01-2021
Now What I need is
for the first Row Test 1 count is 1
for the second row Test 2 count is 1
for the third row Test 1 count is 2
and so on..I need the number of counts till row date.
- parry2k
@ that's exactly what the solution I provided will do:
here is the screenshot:
- ashaykohad
Don't know why I am not getting this result
frequency of visit = CALCULATE(COUNTROWS('bike_job_record'), FILTER( ALL('bike_job_record'), 'bike_job_record'[Entry Time] <=MAX('bike_job_record'[Entry Time])),VALUES('bike_job_record'[bo id]))
only difference is in my case "bo id" is numeric value not the text.
Just remove the Test in first column
- parry2k
@ that type doesn't matter. can you make sure Entry Time column type is date?
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- ashaykohad
Yes, found the issue.
I am trying this for creating new column in the table.
getting required output in the table visualization.
But need this in the new column in the existing table, attaching the screenshot.
As you see I am getting 8 here.
- parry2k
@ I clearly mentioned add this as a measure not sure why you decided to add as a column
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- ashaykohad
Yes, but this is not solving my query.
I need this in the column.
But Thanks for the efforts..
- parry2k
@ use this for column
Column = VAR __d = 'Table (2)'[Date] RETURN CALCULATE ( COUNTROWS ('Table (2)' ) , ALLEXCEPT ( 'Table (2)','Table (2)'[Name] ),'Table (2)'[Date] <= __d )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- ashaykohad
is it possible to create the measure for "count number of rows whose above column values are greater than 1".
Without creating this column seperatly.
output
Name Date count Frequency (Name)
Test1 29-01-2022 1
Test2 30-01-2022 1
Test3 30-01-2022 1
Test1 30-01-2022 2
Test1 31-01-2022 3
Test4 01-02-2022 1
Test3 01-02-2022 2
Test5 02-02-2022 1
So required measeure output = 3
Dont want to create separate column as this is not working for 5L+ entries.
So required direct measure.
Thanks in Advance..
- parry2k
ashaykohad don't take me wrong, do you know what you are looking for? If your original question was answered, you should create another post for your new question.
- ashaykohad
It was easy to ask for the help coonecting to the old anwser.
Earlier tried to add the same in single post , but was difficult to explain in single post..
I have posted new one, if you found the solution, Please help...
- parry2k
ashaykohad what is the link to the new post?
- ashaykohad
Have you check the provide link for new post?