Sum of previous months based on slicer selection

[Timo1980]

Hi all,

Below is a simplified view of my data set.

What i want to achieve with a measure in a card in the reporting pane,  is the total sum of active people calculated depending on date slicer setting (month).

So on the reporting site i have a slicer with months.  Now when I select a specific month in the slicer i get the total sum of active people.

So when i Select Jan the Card shows 1, for Feb it would show 2, Mar=2, May=3.......July=5  etc...

Name	Start Date	Status
A	Jan/21	Active
B	Feb/21	Active
C	Mar/21	Inactive
D	May/21	Active
E	Jun/21	Active
F	Jul/21	Active

I have tried a calculate sum with date filters equal or smaller to, but it only gives the sum of the specific date. Any help greatly appreciated!!

[Anonymous · Accepted solution]

Hi @Timo1980 

Please correct me if I wrongly understood your issue .

(1)Create a calculated column to judge the status for people . If is active return 1 ,then 0 .

column = IF('Employee Master'[Status]="Active",1,0)

(2)Create a calendar date table and display with the form of a slicer .

Table = CALENDAR(DATE(2021,01,01),DATE(2021,12,31))

(3)Create a measure to return the total number of active people .

total active people = CALCULATE(SUM('Employee Master'[column]),FILTER('Employee Master','Employee Master'[Start Date]<=EOMONTH(MAX('Table'[Date]),0)))

The effect is as shown :

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[VijayP]

@Timo1980 
CALCULATE(SUM(COLUMN), FILTER(ALL(TABLE[status], Table[Status]="Active")

[amitchandak]

@Timo1980 , with disconnected date table in slicer

Till Date =
var _max = maxx(allselected(Date), Date[date])
return
calculate(countrows(Table), filter(Table, Table[Start DAte] <=_max))

With connected date slicer

Till Date =
var _max = maxx(allselected(Date), Date[date])
return
calculate(countrows(Table), filter(all(Date), Date[DAte] <=_max))

[Timo1980]

it is not working, i got an issue with the Date[date] fields they remain gray. ..

Table name is 'Employee Master' and Date column [Service Date]

these should all we need, correct?