Cumulative sum based on date range slicer and group by

[navin1491]

I have 2 employees and the sales values ordered by date in the table. I want to create a table where I filter by date and the cumulative sales per employee should be displayed for the corresponding date.

Employee sales table

max_selected = MAX(Query1[Date])

min_selected = MIN(Query1[Date])

Measure= 
CALCULATE(
    (SUMX(VALUES(Query1[ID]),CALCULATE(SUM(Query1[Sales])))),
    FILTER (ALL(Query1),
        [Date] >= [min_selected]
            && [Date] <= [max_selected]
    ))

Can someone please help me here?

[AlB]

Hi  @navin1491

Let's try this:

1. Place a table visual in your report with Query1[ID] in the rows

2. Place Query1[Date] in a slicer

3. Place in values of the table a  measure like: 

CumulativeSales =
CALCULATE (
    SUM ( Query1[Sales] ),
    FILTER ( ALL ( Query1[Date] ), Query1[Date] <= MAX ( Query1[Date] ) )
)

[navin1491]

@AlBThank you so much for you help. There is one more problem that I'm facing now. I also want to filter with the total sales value which would interact with the date slicer. Can you please help me with the creation of total sales value filter that would change when date slicer changes. Thanks!

[AlB]

@navin1491

I don't think I understand what you mean. Can you maybe provide an example to clarify?

[navin1491]

Hi @AlB

In the above image, we have the sales table, date slicer and total sales slicer(assumed). I want to have a slicer with cumulative sales value at ID level which will change when the date range slicer is changed.

For example, when I select only November and December months, the value in the sales filter should automatically change to 2300-4600 based on the example provided.

Please let me know if any other details are needed. Thanks! 

[v-shex-msft]

HI @navin1491,

>>In the above image, we have the sales table, date slicer and total sales slicer(assumed). I want to have a slicer with cumulative sales value at ID level which will change when the date range slicer is changed.

Nope, this is impossible. You can't use one slicer to let other slicer dynamic changed.

Regards,

Xiaoxin Sheng

[AlB]

@navin1491

As @v-shex-msft says, you cannot do that. May I ask what you are trying to achieve? Maybe it can be done in another way. What would be the point of having the Sales slicer change?

[navin1491]

@AlB @v-shex-msft I'm trying to filter by date and also cumulative sales values. For example, if I filter by date, the cumulative sales values only between those dates should be displayed and the sales filter value should also change. And thank you for all your help! Appreciate the help! 🙂

[v-shex-msft]

Hi @navin1491,

Maybe you can try to define query parameter a filter to filter on query editor side, it can affect source table of slicers.

Deep Dive into Query Parameters and Power BI Templates

Regards,
Xiaoxin Sheng

[navin1491]

@AlBI'm sorry the image didn't get attached. 

[navin1491]

I have 2 employees and the sales values ordered by date in the table. I want to create a table where I filter by date and the cumulative sales per employee should be displayed for the corresponding date.

[navin1491]

max_selected = MAX(Query1[Date]) min_selected = MIN(Query1[Date]) Measure = CALCULATE( (SUMX(VALUES(Query1[ID]),CALCULATE(SUM(Query1[Sales])))), FILTER (ALL(Query1), [Date] >= [min_selected] && [Date] <= [max_selected] )) This is the query that I'm using but it returns the sum of all the users, but not the sum of sales values for individual users.

[navin1491]

Image for illustration