snapshot of activity over time

[EF]

Hi all,

I am trying to get a count of drafts cumulatively over time. drafts are unsigned notes (date_signed = 9/9/999); once date_signed has a date it is no longer a draft.

I want to count how many notes were in draft over time (like a historical snapshot).

I have an event log, where each record is a note, and a date table with active relationaship to actual_date and inactive relationship to date_signed. 

sample data:

event_log_id|actual_date|date_signed

A| 12/20/20| 4/1/21

B| 1/1/21| 2/4/21

C| 2/1/21| 2/4/21

D| 2/28/21| 3/1/21

E| 3/1/21| 3/2/21

F| 4/1/21| 9/9/9999

Desired Results

Count of Drafts:

Jan 2021 = 2

Feb 2021 = 2

Mar 2021 = 1

Apr 2021 = 1

I tried this but it didn't give me desired results.

drafts_historical =

var _max1 = max(DateTable[Date])

var _min1 = MIN(DateTable[Date])

return

CALCULATE(COUNTa(event_log[event_log_id]),FILTER(event_log,

event_log[actual_date]<=_max1&&event_log[date_signed]>_max1))

Any advice would be appreciated.

[EF]

@Jihwan_Kim @amitchandak 

Hi,

Sorry for the delayed response.

the solution I am looking for is slightly different then your responses.

what I need is a snapshot at each time point.

for example, if 2 weeks ago (in May) I counted current drafts; notes that were written any time in the past (not just in May) and were not yet signed. I would want to see that number on the May count.

I would think it should be a take on @Jihwan_Kim 's meausre, with one change that only look at signed_notes after max date, so that you are counting the drafts that existed during the time:

Count of Events =
CALCULATE (
COUNTROWS ( Events ),
FILTER (
Events,
Events[actual_date] <= MAX ( 'Calendar'[Date] )
&& Events[date_signed] >MAX ( 'Calendar'[Date] )
)
)

But this still isnt working correctly.

I tried adding crossfilter to remove relationshiop between date table and actual date, but that made no difference.

Any ideas?

[Jihwan_Kim]

Hi, @EF 

I am not sure if I understood correctly your question,  but please check the below.

Count of Events =
CALCULATE (
COUNTROWS ( Events ),
FILTER (
Events,
Events[actual_date] <= MAX ( 'Calendar'[Date] )
&& Events[date_signed] >= MIN ( 'Calendar'[Date] )
)
)

https://www.dropbox.com/s/kg9b8y5fdmkofdi/ef.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@EF , refer to my HR blog on similar topic if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...