Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Time based chart

Hi,

 

i have the columns in table following

 

 

Engagement start time   MIns(Duration )      Department

 

10.00                                  20                            Sales

11.00                                  15                           Marketing

12.10                                   60                          Sales

 

want to show in a chart at which time call happened on daily basis(today)

or  else time taken from engagement start time to end time to progress the call of each department.

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi yuvarajg,

     

    From your description, I create sample data to reproduce your scenario. Firstly, create column "Engagement end time" to get end time, and create table"TimeKey" to display 24 hours daily for further measure analysis.

     

    Engagement end time = 'Table'[Engagement start time]+TIME(0,'Table'[Mins(Duration)],0)

     

    Secondly, create measure "Call_Num" to count call using DAX function below.

     

    Call_Num = CALCULATE(COUNT('Table'[Department]),FILTER(ALLSELECTED('Table'),'Table'[Engagement start time]<=MAX('TimeKey'[Time])&&'Table'[Engagement end time]>=MIN(('TimeKey'[Time]))))

     

    Finally, choose Clustered column chart visual to show at which time call happened on daily.

     

     

     

     

     

     

     

     

     

     

     

     

     

    Here is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETqD8p4o2WBMmqB5UZGmo7QBtNt6ApcdPdXjCb-6_eWswQ?e=YuA7in

     

    If you have any other issue, please feel free to ask.

     

    Best Regards,

    Amy

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi yuvarajg,

     

    From your description, I create sample data to reproduce your scenario. Firstly, create column "Engagement end time" to get end time, and create table"TimeKey" to display 24 hours daily for further measure analysis.

     

    Engagement end time = 'Table'[Engagement start time]+TIME(0,'Table'[Mins(Duration)],0)

     

    Secondly, create measure "Call_Num" to count call using DAX function below.

     

    Call_Num = CALCULATE(COUNT('Table'[Department]),FILTER(ALLSELECTED('Table'),'Table'[Engagement start time]<=MAX('TimeKey'[Time])&&'Table'[Engagement end time]>=MIN(('TimeKey'[Time]))))

     

    Finally, choose Clustered column chart visual to show at which time call happened on daily.

     

     

     

     

     

     

     

     

     

     

     

     

     

    Here is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETqD8p4o2WBMmqB5UZGmo7QBtNt6ApcdPdXjCb-6_eWswQ?e=YuA7in

     

    If you have any other issue, please feel free to ask.

     

    Best Regards,

    Amy