cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamOvermars
Frequent Visitor

Filtering rolling total based on value in another column

Hello, 

I have this sample table 

Box#ItemCategoryDate ProducedTruck2 RequestedTruck3 Requested
B100AAutos2/1/202140
B100BHome2/5/202111
B200A2Home2/8/202102
B300A3Autos2/12/202113

 

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?

 

 

 

1 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))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

7 REPLIES 7
richbenmintz
Super User
Super User

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])


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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

richbenmintz_0-1633112547459.png

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

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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

SamOvermars_1-1633540725648.png

 

SamOvermars_0-1633540568779.png

 

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))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

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

SamOvermars_0-1633547462549.png

 

Hi @SamOvermars ,

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors