Forum Discussion

fraitaan's avatar
fraitaan
Frequent Visitor
3 years ago
Solved

Calculate sick headcount

Hi,  I've been searching around for a while and I've found a few formulas that somewhat works but doesn't go all the way.  I'm using this formula to calculate headcount at our company, and it w...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi fraitaan ,

     

    According to your description, you want to calculate the count of the sick people according to every date.

    Here are the steps you can refer to :

    (1)My test date is the same as yours.

     

    (2)We can create a date table as a dimension table, and we do not need to create a relationship between two tables.

    Date = CALENDAR( DATE(2022,11,1) ,DATE(2022,11,16))

     

    (3)Then we can create a measure:

    Measure = var _cur_date = MAX('Date'[Date])

var _t = FILTER( 'Table' , 'Table'[Sick from]<=_cur_date && 'Table'[Sick too]>= _cur_date)

return

COUNTROWS(_t)+0

     

    (4)Then we can put the fields we need on the visual and we can meet your need , the result is as follows:

    Best Regards,

    Neeko Tang

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi fraitaan ,

 

According to your description, you want to calculate the count of the sick people according to every date.

Here are the steps you can refer to :

(1)My test date is the same as yours.

 

(2)We can create a date table as a dimension table, and we do not need to create a relationship between two tables.

Date = CALENDAR( DATE(2022,11,1) ,DATE(2022,11,16))

 

(3)Then we can create a measure:

Measure = var _cur_date = MAX('Date'[Date])

var _t = FILTER( 'Table' , 'Table'[Sick from]<=_cur_date && 'Table'[Sick too]>= _cur_date)

return

COUNTROWS(_t)+0

 

(4)Then we can put the fields we need on the visual and we can meet your need , the result is as follows:

Best Regards,

Neeko Tang

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