Sum number of days absent

[rosscortb]

Hello,

I'm trying to sum the the number of days absent from last day absent, back one year.

So  if someone had one day absent on 1/12/2018 and then two days absent 01/06/2019

then the answer would be two

Tried couple of ways

Test = CALCULATE(SUM('DB Absence'[Total absence days]),DATESBETWEEN('DB Absence','DB Absence'[Absence Start Date],'DB Absence'[Absence End Date]))

Test = CALCULATE(SUM('DB Absence'[Total absence days]),DATESBETWEEN('DB Absence','DB Absence'[Absence Start Date],TODAY()))

Any ideas?

Thanks

Ross

[parry2k]

@rosscortb can you share sample data ? It is not very clear how data is modelled and how you table looks like? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[rosscortb]

---

---

Hopefully this works - Demo

I have a calendar table in my real BI but not added on here as not sure its relevant for this measure?

Thanks

Ross

[v-lili6-msft · Accepted solution]

hi, @rosscortb 

You could try to use SUMX Function as this formula:

Measure = CALCULATE(SUMX(Sheet1,DATEDIFF(Sheet1[Absence Start Date],Sheet1[Absence End Date],DAY)+1))

If not your case, please share more sample data and your expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin