Accumulating count

[Roinka01]

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

[parry2k]

@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.⚡

[Roinka01]

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.

[parry2k]

@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-ca...

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 name

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.⚡

[Roinka01]

Thank you but i am afraid it doesnt work. 😞

Also i dont understand it.

SUMX should be used when we are performing some formula over more than a single column all along the rows. But here we are only counting over a single column.

[parry2k]

@Roinka01 not sure why it is not working. and if you simply answer that it is not working, I don't know how I can help further. You have to share what measure you added, what result you are getting? Are there any filters. Provide as much detail as you can otherwise we will keep on going in circles.

[Roinka01]

Hi,

sorry for the delay i had to leave office.

the measure is:

SumOfTotTv = sumx(filter(ALLSELECTED('Calendar'[FirstDayOfMonth]) , 'Calendar'[FirstDayOfMonth]<MAX('Calendar'[FirstDayOfMonth])),CALCULATE(DISTINCTCOUNT('Calendar'[FirstDayOfMonth])))

and what i get is (while i have tens of thoussands of such events):

Thanks for your help.