Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Rolling Stock Position With Gaps In Months

Hi   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 da...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

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

lbendlin's avatar
lbendlin
Super User
2 years ago

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