Forum Discussion
Filtering rolling total based on value in another column
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?
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))
7 Replies
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])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.
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.
