Find sum based on filter

[unnijoy]

I have two column Month and Status (1 or 0). In month i have month from Jan 2001 to June 2019. I have a month slicer. I need help in creating a formula in sucha way that when i select a particular month i need to get the sum on all 1 from Jan 2001 till up to selected month. Below is the example.

Month	Status
Jan-01	0
Feb-01	1
Mar-01	1
Apr-01	0
May-01	1
Jun-01	0
Jul-01	1
Aug-01	1
Sep-01	1
Oct-01	0
Nov-01	1
Dec-01	0
Jan-02	1
Feb-02	0
Mar-02	1
Apr-02	0
Jan-16	1
Feb-16	0
Mar-16	1
Jan-19	1
Feb-19	1
Mar-19	0
Apr-19	1
May-19	0

From the above example from the slicer if i select April 2019 i should the sum of 1 from the begining (Jan 2001) till April 2019 and give 14. And if i select Mar 2002 it should calculate the sum of 1 from Jan 2001 till up to Mar 2002 and gvie the output as 9.

[v-xuding-msft · Accepted solution]

Hi @unnijoy ,

By my test, you could create a irrelative table as a slicer to filter the values. I create a sample using two ways you can have a try.

1. Create a new table. And the values I added is the same as your sample data. There is no relationship between two tables.
2. Create measures.

Method 1:

Note : it will show the original values and the expected total.

Measure =
var a = SELECTEDVALUE(Table2[Month ])
return CALCULATE(SUM(Table1[Status]),FILTER(Table1,MAX(Table1[Month ])<=a))

Measure 2 = IF(ISFILTERED(Table2[Month ]),CALCULATE(SUMX(Table1,Table1[Measure]),ALLEXCEPT(Table1,Table1[Month ])),SUM(Table1[Status]))

Method 2:

Note: It will show blank by default.

Measure 3 = IF(ISFILTERED(Table2[Month ]) && MAX(Table1[Month ])<=SELECTEDVALUE(Table2[Month ] ),SUMX(FILTER(ALL(Table1),'Table1'[Month ] <=SELECTEDVALUE(Table2[Month ])),Table1[Status]))

Best Regards,

Xue Ding 

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