Filtering rolling total based on value in another column

[SamOvermars]

Hello, 

I have this sample table 

Box#	Item	Category	Date Produced	Truck2 Requested	Truck3 Requested
B100	A	Autos	2/1/2021	4	0
B100	B	Home	2/5/2021	1	1
B200	A2	Home	2/8/2021	0	2
B300	A3	Autos	2/12/2021	1	3

I wanted a line chart that will show a rolling total for how many items were produced for lets say truck 2 or truck 3 (where truck requested is not 0)

so I used this dax measure for the value , and applied a filter of truck 2 but still the count seems off

Measure = CALCULATE( COUNTX(FILTER('table','table'[Category] = "Autos"),'table'[Item])
,FILTER( ALLSELECTED( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced])))

If I go to the table in the data modeling tab and filter Truck2 > 1 and the type "Auto" I will see 90 rows in my original data, but with the line visual the last value was 120.

What im I missing here?

[richbenmintz]

Hi @SamOvermars,

If you replace

ALLSELECTED( 'table'[Date Produced])

 with 

ALL( 'table'[Date Produced])

 does that do the trick, now you might have to add a min date to your filter like

 'table'[Date Produced] >= MIN('table'[Date Produced]) && 'table'[Date Produced] <= MAX('table'[Date Produced])

[SamOvermars]

Hi @richbenmintz , Thank you for your response, I tried with that but it started showing me the items count every day in the line chart. which is not what I desired. I needed the rolling total that can be filtered. Seems like the issue I have is with the COUNTX part instead of the date. Just not sure how to tackle it.

[richbenmintz]

Hi @SamOvermars ,

Using your sample data and assuming you are looking to count the rows cummulatively, the following Measure should work

Measure = CALCULATE( COUNTROWS('table')
,FILTER( ALL( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced])))

produces the following line chart

If you need a different outcome please include a more representative set of data and a screen cap of the desired result.

[SamOvermars]

Thank you @richbenmintz  Almost!It worked but its not letting me filter the visual.

Is there any reason why the line chart looks like this after adding a filter to the visual where truck 2 is not 0

[richbenmintz · Accepted solution]

Hi @SamOvermars ,

Maybe you should filter the data in the measure

Measure = CALCULATE( COUNTROWS('table')
,FILTER( ALL( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced]) && 'table'[Produced Truck2 Requested]<>0))

[SamOvermars]

@richbenmintz  Im not sure how Power BI is handeling this in the backend. but the measure is showing it like this now.

[richbenmintz]

Hi @SamOvermars ,

Could you provide a sample pbix file and the expected outcome as a visual?