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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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