Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I want to count the number of clients who have traded within this current period (15days) by periodid.
I have a jobs table and a calendar table. this is connected via 1:N relationship from calendar[single_dates] to jobs[Job_date]
Calendar table: Jobs table:
Single_date Periodid job_date job_ID client_ID
01/07/2021 1 01/07/2021 123123 A
02/07/2021 1 01/07/2021 4234234 A
... .....
16/07/2021 2
17/07/2021 2
i've wrote a measure to retrieve the current periodid:
Solved! Go to Solution.
Hi @Bokchoy ,
A slight adjustment has been made to your second dax, see:
test =
CALCULATE (
DISTINCTCOUNT ( job[client_id] ),FILTER(ALL('calendar'),
calendar[periodid] = [this period id]
))
And final show the below:
Wish it is helpful for you!
Best Regards
Lucien
Hi @Bokchoy ,
A slight adjustment has been made to your second dax, see:
test =
CALCULATE (
DISTINCTCOUNT ( job[client_id] ),FILTER(ALL('calendar'),
calendar[periodid] = [this period id]
))
And final show the below:
Wish it is helpful for you!
Best Regards
Lucien
does the this period id measure work?
could you pls provide more sample data and expected output?
Proud to be a Super User!
Hi Ryan,
Yes, the measure works. Right now the measure is returning "2" as we are in the second period. it should return "3" on the 1st of Aug. (Fy start is 01/07 end is 30/06)
Unfortunately, i cant provide more sample data but I think the structure is pretty simple.
- the calendar table has every single day as an individual row from 07/2020 to 06/2022, grouped by period id and year.
- the jobs table simply records when a client traded. Each row has a unique job_id, job_date, and other job information. there is no limit to how many jobs a client can trade each day.
add a column in jobs table
period = RELATED('Calendar'[Period])
then create a measure
measure = CALCULATE(DISTINCTCOUNT(Jobs[cline_ID]),FILTER(Jobs,Jobs[period]= maxx(FILTER('Calendar','Calendar'[Single_date]=today()),'Calendar'[Period])))
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |