Calculating Running Totals of Days for Bradford Score for project

[Anonymous]

I have these project to calculate the bradford factor score (BFS) of each employee and show its evoulution. However, I need to calculate the total days absent as shown in the column total (Excel File) so that I can calculate the BFS in evolution. The client wants me to try and make all calculation in Power BI. Any help please?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create columns

Day = FORMAT([Date],"Ddd")

M = MONTH([Date])

year = YEAR([Date])

Total = CALCULATE(SUM('Table'[Days]),FILTER(ALLEXCEPT('Table','Table'[Name]),[AbsenceID]<=EARLIER('Table'[AbsenceID])))

Besides, according to this article, i calculate the Bradford Factor as below:

Create measures

total number of separate absences = DISTINCTCOUNT('Table'[AbsenceID])

total number of days’ absence = SUM('Table'[Days])


Bradford Formula = POWER([total number of separate absences],2)*[total number of days’ absence]

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Nor very clear

Seem like cumulative

New column

Cumm = sumx(filter(Table,[employee name] = earlier([[employee name]) && [Date] < earlier([Date])),[Day])

New measure

Cumm Sales = CALCULATE(SUM(Table[Day]),filter(date,date[date] <=maxx(date,date[date])))

Or YTD
YTD Sales = CALCULATE(SUM(Table[Day]),DATESYTD((Date[Date]),"12/31"))

[parry2k]

@Anonymous the best would be to share sample data in excel or table format and expected output. 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