topic Re: Rolling Stock Position With Gaps In Months in DAX Commands and Tips

Rolling Stock Position With Gaps In Months

Anonymous — Tue, 25 Jun 2024 12:21:26 GMT

I've loaded Item Ledger Entries table from Business Central into Power BI.

I'd like to see my rolling stock position by month, my issue is that if there's a month with no stock change / no data, it will show as 0 when it should take the last available data point e.g. if as of February there was £1.3m of stock value and no data in March then show £1.3m.

I'm currently using the below measure:

Cumulative Inventory Value =

CALCULATE(

SUM('Item Ledger Entries'[costAmountActual]),

FILTER(

ALL('Item Ledger Entries'),

'Item Ledger Entries'[postingDate] <= MAX('Item Ledger Entries'[postingDate]

)

Re: Rolling Stock Position With Gaps In Months

lbendlin — Wed, 26 Jun 2024 01:18:28 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Rolling Stock Position With Gaps In Months

Anonymous — Wed, 26 Jun 2024 01:57:33 GMT

Hi Anonymous ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

Measure = VAR _Value = SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) && 'Table'[Stock Value] > 0 ), 'Table'[Stock Value] ) RETURN IF ( SUM ( 'Table'[Stock Value] ) <> 0, SUM ( 'Table'[Stock Value] ), _Value )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Rolling Stock Position With Gaps In Months

Anonymous — Wed, 26 Jun 2024 08:47:46 GMT

Appreciate the help. Unfortunately isn't the solution.

Below is what I should expect to see.

The issue is if there's absolutely no data for a specific month such as below (missing May) then it should take the previous months total

Re: Rolling Stock Position With Gaps In Months

Anonymous — Thu, 27 Jun 2024 06:31:01 GMT

Hi Anonymous ,

I changed my DAX code.

Measure = VAR _currentdate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Stock Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= _currentdate ) )

Then I get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Rolling Stock Position With Gaps In Months

Anonymous — Thu, 27 Jun 2024 08:47:31 GMT

Close but doesn't address if the table is missing data.

Let's say I've created a date table for 01/01 - 31/12 and connected it to the stock table. If I drag in the date table date column, the measure would be blank for 02/01, 03/01 etc.. when actually the stock position as of 03/01 would still be 10 not blank.

Edited with clearer expected results

Re: Rolling Stock Position With Gaps In Months

Anonymous — Fri, 28 Jun 2024 05:18:45 GMT

Hi Anonymous ,

I'm sorry that Power BI doesn't have such a smart feature at the moment, and the requirement that you mentioned that an undisplayed date still needs to be returned to the previous value can't be realized yet.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Rolling Stock Position With Gaps In Months

Anonymous — Fri, 28 Jun 2024 08:20:35 GMT

No problem, thank you so much for your help

Re: Rolling Stock Position With Gaps In Months

DataInsights — Fri, 28 Jun 2024 14:01:01 GMT

Anonymous,

This can be solved with a date table. There are various ways to create a date table--here's a DAX calculated table:

Date = ADDCOLUMNS ( CALENDARAUTO (), "Month Start Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) )

Create a 1:* relationship between the date table and data (fact) table:

Create measures:

Sum of Stock Value = SUM ( 'Table'[Stock Value] )

Running Total = CALCULATE ( [Sum of Stock Value], FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Result:

Whenever possible, use dimension table fields in visuals (e.g., 'Date'[Month Start Date]).