Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EF
Helper II
Helper II

snapshot of activity over time

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.
3 REPLIES 3
EF
Helper II
Helper II

@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
Super User
Super User

Hi, @EF 

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

 

Picture1.png

 

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

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


Go to My LinkedIn Page


amitchandak
Super User
Super User

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.