Fetch last non-blank value for any date with multiple filters

[Revolution]

I am trying to produce the sum of a running total for all SKU's selected by slicers on any selected date.

I have a table with transaction data as follows:

Warehouse  |

SKU |

TxDate |

DayQty |

RT (Running Total)

This measure works for each SKU individually:

RT by Size = 
VAR d = 
    SELECTEDVALUE('Date'[Date])
RETURN
    CALCULATE(
        LASTNONBLANKVALUE(StkDayQty[TxDate], SUM(StkDayQty[RT])),
        StkDayQty[TxDate] <= d,
        CROSSFILTER('Date'[Date], StkDayQty[TxDate], none)
    )

I created a table that assigns a unique ID (IdWhseStk) for each SKU in each warehouse and attempted to create a measure that will sum RT for all selected by slicers:

RT Total = SUMX(
    VALUES(WhseStk[IdWhseStk]),
    [RT by Size])

This does not work: 

Also tried this with the same result:

RT Selected = 
VAR _RTSize = [RT by Size]

RETURN
if( isfiltered(WhseStk[IdWhseStk]) ,
SUMX (
VALUES ( WhseStk[IdWhseStk] ),_RTSize))

I even tried to merge the entire date table to each SKU before calculating the RT in Power Query so that I have a distinct RT for every SKU on every date but it results in millions of rows.

I can't find a solution that I can apply to my dataset. Any advice would be much appreciated.

[MarkLaf]

Not sure you need a helper table. I assume your main issue is that your date slicer is filtering the VALUES ( WhseStk[IdWhseStk] ) that you are iterating over. Try:

RT Total = 
SUMX(
    CALCULATETABLE( VALUES(StkDayQty[SKU]), REMOVEFILTERS( 'Date' ) ),
    [RT by Size]
)

[Revolution · Accepted solution]

This doesn't work because I have the same SKU for different warehouses. Hence the helper table which was created in SQL at the source. I discovered duplicates so made a new table containing unique IDs for each SKU+Warehouse. I now have it working. 

[scee07]

From what I understand of the above, this might be a visual calculation case. I took the canonical Contoso sales data and calcualuated the profit as calculated column in the visual (Just to show that a calculated column in the visual works)

In my data there is a product key and date key that play the roles of your field. 

Then you can use the running sum as another calculated column in the visual: 

RunningSum = RUNNINGSUM([Profit])

Then I selected a few date keys with a slicer and did another date slicer with "before date" settings

You can see that this produces indeed the running totals acrosss the product keys

Hope this helps.

The approach definetely reduces filter context complexity. It is a matter of taste. 

Best regards 

Christian

[Dangar332]

Hi, @Revolution 

It's better if you provide Some sample data