Problem with cumulation total when adding filters

[tragonneau]

Hello,

I have the following table :

StockTable

I have created this measure to have the cumulative total of the Qty :

qty_cumul = 
CALCULATE(
	SUM(Stock[Qty]);
	FILTER(
		ALL(Stock[date]);
		Stock[date] <= MAX(Stock[date])
	)
) 

It works as I want. When I want to see the evolution of the cumulative total for specific entities (let's say ENT_1 and ENT_2), it still works well. 

HOWEVER i have a problem when I add a visual filter to see the evolution of the total for a specific period. The result is not the sum of the cumulative quantity of ENT_1 and ENT_2 (whereas it is when i don't use the filter on the date).

I realised this graph is displaying alternatively the cumulative total of ENT_1 and ENT_2 which is not what I want.

When I added the filter on the date i expected my bottom chart to be a "zoom in" of the top chart.

I don't understand why it doesn't work as I want. I feel like I am missing something.

Can you help me please.

I am working in DirectQuery mode but to explain my issue i created a custom table.

Here the file i used to do the screenshots:

https://www.dropbox.com/s/sekh8g8ht03ibbs/StockCumulativeTotal.pbix?dl=0

[v-qiuyu-msft]

Hi @tragonneau,

From the .PBIX file you provided, you have added filter for the bottom line chart in Visual Level Filter property, which only apply to the bottom line chart. This filter setting will not apply for other line chart or table visual. So the data for the same date display as different values in those two line charts. See: Add a filter to a Power BI report (in Editing view).

In your scenario, please remove visual level filter, add the same filter on page level filter, this filter will apply for all visuals in this report page. Also you need to apply the filter like below:

qty_cumul = CALCULATE(
    SUM(Stock[Qty]),
    FILTER(
     ALLSELECTED(Stock),
     Stock[date] <= MAX(Stock[date])
    )
)

Please download and run the attached .PBIX file to see if it meet your requirement.

Best Regards,
Qiuyun Yu

[tragonneau]

@blopez11

Thanks, it works well with the DateTable. I don't know why i didn't use it.

@v-qiuyu-msft

Thanks, but it's not what i wanted to do.

My explanations were confusing, i'm sorry.

Maybe it will make more sense with the following screenshot.

I wanted to see the values for a shorter period : the top side chart represents the values in the red rectangle.

[blopez11 · Accepted solution]

Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table.  However, I haven't tried in DirectQuery mode

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)