Head Count using Sum

[adamSW157]

I'm trying to create a Headcount to display a value by time/ over months, i have a working formual but my data isnt in the correct format. how would i go about changing it or adapt the formual to work? I would also need the lastest 'value' from any updates to be used in the head count.

My current table looks like this

Unique ID	Record Type	Date entered	Value
111	Added	01/08/2018	20
111	Cleared	10/10/2018
112	Added	01/09/2018	20
112	update	15/09/2018	15
112	Cleared	10/11/2018

This is the data format that works with the following formula

total =
CALCULATE(
 SUM(Sheet3[Value]),
 FILTER(Sheet3,(Sheet3[Added ]<= LASTDATE('Date'[Date])
    && Sheet3[Cleared ] >= FIRSTDATE('Date'[Date]))
    ))

Unique ID	Added	Cleared	Value
111	01/08/2018	10/10/2018	20
112	01/09/2018	10/11/2018	15

This is the end goal a summary of all values added accross months

[LivioLanzo]

Hi @adamSW157

I am trying to understand your logic. Should you not have 55 in September and October, since both 111 and 112 are open during this month. And also why in November you just have 15? Shouldn't it be 35?

[adamSW157]

I'm only interested in showing what the most recent value was accross the time it was 'Open' for I.e 15 for 112 and 20 for 111.

Therefore its 35 as they both occur in September and October and only 15 for Novemeber as 111 was clear on 10/10/2018

I'm happy with the fromula i'm using just need a hand either changing the data set within BI to the second table or finding away to pick up the 'added' and 'cleared' dates and inserting them into the formula.  

[LivioLanzo]

@adamSW157

I would remodel your table as per my attached file and then the measure because a simple SUM

https://1drv.ms/u/s!AiiWkkwHZChHjycWYZPalNOlrQsT

[adamSW157]

@LivioLanzo that's great didn't know you could write such in-depth formulas with BI.
However with your way i wouldn't be able to drill down from months to days/weeks.

[LivioLanzo]

It is possible to change the model to make the analysis by days possible, I thought you wanted to do it at month level since you wrote "I'm trying to create a Headcount to display a value by time/ over months"

[adamSW157]

@LivioLanzo your not wrong that was originally what i asked for and i'm grateful for your help. But as i use BI more i can see how going down another level may be useful in some cases.