Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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?
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
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.
@EF , refer to my HR blog on similar topic if that can help
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
72 | |
49 |
User | Count |
---|---|
142 | |
137 | |
110 | |
69 | |
55 |