Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Labor Utilization Calculation

I am new to DAX and need help with an equation that needs to count the number of occurrences when a date field shows "same date".

DateEmployeIDCategoryID 
11/1/202211  
11/1/202221  
11/1/202231  
11/1/202242  
11/1/202252  
11/1/202262 0.882353
11/2/202271  
11/2/202281  
11/2/202291  
11/2/2022101  
11/2/2022111  
11/2/2022122  
11/2/2022132  
11/2/2022142 0.843137
11/3/2022151  
11/3/2022161  
11/3/2022171  
11/3/2022181  
11/3/2022192  
11/3/2022202  
11/3/2022212  
11/3/2022222 0.862745

 

How do I count the number of Category ID entries within same date range, then create the calc of

(Total # Emps - Category Counts)/total # Emps

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    According to your description, you want to " count the number of Category ID entries within same date range". Right?

    But fo the "0.882353,0.843137,0.862745" , i can not kown how to calculate this value.

    My test data is the same as yours.

    Based on my understand , the "2022/11/1" for the count fo category is 2?

    If this , you can click "New Column":

    Column = var _rowdate =  [Date] 
var _t =SELECTCOLUMNS( FILTER('Table','Table'[Date]=_rowdate) , "Category" , [CategoryID])
return
COUNTROWS( DISTINCT(_t))

    Then you can get the the number of Category ID entries within same date range.

    If this does not meet your needs, can you provide us with your expected output and your detailed calculation logic?

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Anonymous 

    According to your description, you want to " count the number of Category ID entries within same date range". Right?

    But fo the "0.882353,0.843137,0.862745" , i can not kown how to calculate this value.

    My test data is the same as yours.

    Based on my understand , the "2022/11/1" for the count fo category is 2?

    If this , you can click "New Column":

    Column = var _rowdate =  [Date] 
var _t =SELECTCOLUMNS( FILTER('Table','Table'[Date]=_rowdate) , "Category" , [CategoryID])
return
COUNTROWS( DISTINCT(_t))

    Then you can get the the number of Category ID entries within same date range.

    If this does not meet your needs, can you provide us with your expected output and your detailed calculation logic?

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly