Count x based on periodid

[Bokchoy]

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:

this period id = CALCULATE(SELECTEDVALUE(calendar[periodid]), calendar[SingleDate] = TODAY())

However my below measure does not work
calculate(distinctcount(job[client_id]), calendar[periodid] = this period id)

Could someone please give me a hand.

Thanks in advance.

[v-luwang-msft · Accepted 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

[ryan_mayu]

@Bokchoy 

does the this period id measure work?

could you pls provide more sample data and expected output?

[Bokchoy]

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.

[ryan_mayu]

@Bokchoy 

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])))