March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
90 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |