Stop cumulative from resetting when filtering by month

[DevidsU]

Hi,

When using this expression the cumulative total shows correct values but it resets when I slice it by single month.

CALCULATE (
SUM ( ITEM_Ledger_entry[Quantity] ),
FILTER (
ALLSELECTED( ITEM_Ledger_entry ),
ITEM_Ledger_entry[Posting_Date] <= MAX ( ITEM_Ledger_entry[Posting_Date]
)))

And when I use the ALLEXCEPT version of expression the cumulative total shows wrong cumulatives or it selects way too many but it doesn't reset when I filter it by month, which is exactly what I need.

CALCULATE (
SUM(ITEM_Ledger_entry[Quantity]), FILTER( ALLEXCEPT(ITEM_Ledger_entry, ITEM_Ledger_entry[Posting_Date].[Date]), ITEM_Ledger_entry[Posting_Date] <= MAX(ITEM_Ledger_entry[Posting_Date])))

I tried combining both ALLSELECTED with except but that didn't seem to work.

Any help will be appreciated!

[DevidsU · Accepted solution]

Seems like this one has the desired result I needed.

VAR currentYear = MAX(ITEM_Ledger_entry[Posting_Date].[Year])
VAR currentProduct = MAX(ITEM_Ledger_entry[Item_Description])
VAR currentPostingDate = MAX(ITEM_Ledger_entry[Posting_Date])
RETURN
 
 SUMX( 
        FILTER (
                ALL ( ITEM_Ledger_entry ),
                     ITEM_Ledger_entry[Item_Description] = currentProduct &&
                     ITEM_Ledger_entry[Posting_Date].[Year] <= currentYear &&
                     ITEM_Ledger_entry[Posting_Date] <= currentPostingDate 
                   
            ),
            ITEM_Ledger_entry[Quantity]
        )
    

[amitchandak]

@DevidsU , waht do mmeas by resetiing here ?

Can you share sample data and sample output in table format?

Try with all

CALCULATE (
SUM ( ITEM_Ledger_entry[Quantity] ),
FILTER (
ALL( ITEM_Ledger_entry ),
ITEM_Ledger_entry[Posting_Date] <= MAX ( ITEM_Ledger_entry[Posting_Date]
)))