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

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

You can try the following 5 measures:

Total Jumbo = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<= MAX(Dim_Date[Date])),'Table'[Storage Type] = "Jumbo")+0

Total Pallet = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<= MAX(Dim_Date[Date])),'Table'[Storage Type] = "Pallet")+0

Total = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date]<=MAX(Dim_Date[Date])))

% Jumbo = [Total Jumbo]/[Total]

% Pallet = [Total Pallet]/[Total]

 

For more details, please refer to the sample pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfHgP-6ymjhOt-eElIg0J5AB16pWEqFcEeYAe0c5ZMXvqw?e=BPsHbW

 

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