Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How Many Days Someone Works in a Row?

Hi!   Please help me with cummulative value with some condition. So I have data consist of date and so many Name. I need to keep in track of how many days someone work in a row until they rest. Th...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    You can add a calculated column with below DAX. 

    Expecting result = 
var _preRestDate = MAXX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]<EARLIER('Table'[Date]) && 'Table'[Working?]=0), 'Table'[Date])
var _firstWorkDate = MINX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]>_preRestDate && 'Table'[Working?]=1), 'Table'[Date])
return
IF('Table'[Working?] = 0, 0, SUMX(FILTER('Table', 'Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]>=_firstWorkDate && 'Table'[Date]<=EARLIER('Table'[Date])), 'Table'[Working?]))

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi Anonymous 

 

You can add a calculated column with below DAX. 

Expecting result = 
var _preRestDate = MAXX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]<EARLIER('Table'[Date]) && 'Table'[Working?]=0), 'Table'[Date])
var _firstWorkDate = MINX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]>_preRestDate && 'Table'[Working?]=1), 'Table'[Date])
return
IF('Table'[Working?] = 0, 0, SUMX(FILTER('Table', 'Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Date]>=_firstWorkDate && 'Table'[Date]<=EARLIER('Table'[Date])), 'Table'[Working?]))

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you so much! This is brilliant