topic Fetch last non-blank value for any date with multiple filters in DAX Commands and Tips

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

Revolution — Fri, 07 Jun 2024 23:22:35 GMT

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.

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

Dangar332 — Sat, 08 Jun 2024 14:41:48 GMT

Hi, Revolution

It's better if you provide Some sample data

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

scee07 — Mon, 10 Jun 2024 13:53:33 GMT

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

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

MarkLaf — Mon, 10 Jun 2024 17:34:09 GMT

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

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

Revolution — Thu, 13 Jun 2024 11:07:41 GMT

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.