Forum Discussion
Accumulating count
Hi there,
I am using a line and stacked column chart.
I would like to present for every month the total count of events created so far (not just for this month but everything until this month).
e.g. Jan - 10 events, Feb - 20, March - 15.
Then i would like to see on the graph on Jan - 10, on Feb 30, On March 45 and so on.
On the shared axis i have the first day of the month (which is a date taken from table Calendar defind at the DB).
At column values i have a measure called - NoOfEvents.
My measure is defined as NoOfEvents=CALCULATE(DISTINCTCOUNT(MyTable[event_id]), Filter(table2, some filter))
The problem is that i can see the count per month and not the accumulated count per month. So insetad of seeing all the events untill this month i can only see the events created on that month.
I get Jan - 10 events, Feb - 20, March - 15.
instead of
Jan - 10, on Feb 30, On March 45
Please advise how shold i change my measure to get the accumulated count?
Thank you
6 Replies
Roinka01 I have answered the similar question at this post
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
Are you refering to this code:
SUMX(FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date]<= MAX('Calendar'[Date])),CALCULATE(DISTINCTCOUNT(TimeLog[Rig_Name])))can you explain it?
Not sure how to translate it to my example.
Roinka01 As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/After you add date dimension and added relationship with your table, the following expression will work.
Running Total = SUMX( FILTER( ALLSELECTED('Calendar'[Date]), --this is new calendar/date dimension you add to the model 'Calendar'[Date]<= MAX('Calendar'[Date]) ), CALCULATE(DISTINCTCOUNT(TimeLog[Rig_Name]))) --this will your table name and column nameI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
