Forum Discussion

Bokchoy's avatar
Bokchoy
Icon for Helper II rankHelper II
5 years ago
Solved

Count x based on periodid

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.

 

 

 

Need Help
Show and Tell
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

4 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Bokchoy 

    does the this period id measure work?

    could you pls provide more sample data and expected output?

    • Bokchoy's avatar
      Bokchoy
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

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