Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure using current inventory measure

Hi, I'm currently working on an inventory report, so I have a main table with inventory movement posting and also a date table. There are various attributes for each entry, such as location (plant), ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    I have create a Inventory_on_date table by dax:

    Inventory_on_date =
VAR MinDate =
    MIN ( 'Posting Table'[Posting date] )
VAR MaxDate =
    MAX ( 'Posting Table'[Posting date] )
VAR table1 =
    CROSSJOIN (
        VALUES ( 'Posting Table'[Plant Code] ),
        CALENDAR ( MinDate, MaxDate )
    )
VAR table2 =
    SUMMARIZE (
        table1,
        [Date],
        'Posting Table'[Plant Code],
        "Jumbo1",
            CALCULATE (
                SUM ( 'Posting Table'[Quantity] ),
                FILTER (
                    'Posting Table',
                    'Posting Table'[Storage Type] = "Jumbo"
                        && 'Posting Table'[Posting date] = EARLIER ( [Date] )
                )
            ),
        "Pallet1",
            CALCULATE (
                SUM ( 'Posting Table'[Quantity] ),
                FILTER (
                    'Posting Table',
                    'Posting Table'[Storage Type] = "Pallet"
                        && 'Posting Table'[Posting date] = EARLIER ( [Date] )
                )
            )
    )
RETURN
    SUMMARIZE (
        table2,
        [Date],
        'Posting Table'[Plant Code],
        [Jumbo1],
        [Pallet1],
        "Jumbo",
            SUMX (
                FILTER (
                    table2,
                    [Date] <= EARLIER ( [Date] )
                        && 'Posting Table'[Plant Code] = EARLIER ( 'Posting Table'[Plant Code] )
                ),
                [Jumbo1]
            ),
        "Pallet",
            SUMX (
                FILTER (
                    table2,
                    [Date] <= EARLIER ( [Date] )
                        && 'Posting Table'[Plant Code] = EARLIER ( 'Posting Table'[Plant Code] )
                ),
                [Pallet1]
            )
    )

     

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EY7HlvJxP9lCuqPBlCT0mdYBnbEMT5-5XgUJNb6SuD5N8g?e=mK8GQ9

     

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

     

    Best Regards,

    Dedmon Dai

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-deddai1-msft,

 

What do you mean by adding the attributes from the summary table? I currently have a slicer for storage location with 118 distinct values that I would like to include. Does this mean that for the inventory_on_date table, I would have to cross join storage location, plant code (11 values), and date (over 2 years of dates - 735 values), resulting in 954,030 rows?

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Since your fact table does not contain continuous date columns, I have to use crossjoin to get continuous date columns. I am not clear about the logical relationship between your storage location and Plant Code. If you find this logical relationship, you can use dax directly add storage location to summary table .

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-deddai1-msft 

      Hi Dedmond, the storage location is a subdivision of plant code and you can derive plant code from storage location. So I guess I can just crossjoin storage location and date to get a continuous date column for each storage location. Then, I can derive plant code from storage location, which gives me about 87,000 rows, which is much less than the other. Does that sound like the best method?

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        It is all depending on your raw data.

         

        Best Regards,

        Dedmon Dai