Measure using current inventory measure

[Anonymous]

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), material, storage type (jumbo/pallet), etc. I need a measure that will calculate the monthly % average of inventory stored in jumbo vs pallet. I also have slicers from the main table in the report, so the measure should work with it. 

For each month, the calculation would be as follows for % pallet: (sum of daily inventory levels stored on pallets)/(sum of total daily inventory stored on pallets and jumbo). The same for % jumbo.

I cannot store daily inventory levels in a dax table because the current posting table encompases two years worth of information (~a million rows) with many materials, locations, etc.

This is my current measure for inventory on hand:

Inventory on hand = 
var _currdate=MAX('Date Table'[Date])
return

CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLSELECTED('Date Table'[Date]),
        ISONORAFTER('Date Table'[Date], _currdate, DESC)
    )
)

When plotted against the date field, I can get daily inventory levels.

Here's some sample data for posting table:

Quantity	Material Code	Posting date	Storage Type	Plant Code
136.85	M10556	18-Oct-20	Jumbo	5320
66	34690D00AO	25-Oct-20	Pallet	5320
-60	M21109B	25-Nov-20	Jumbo	5320
66	34690D00AO	25-Nov-20	Jumbo	5320
-20	M26118	16-Dec-20	Jumbo	5320
-50.8	34690D01AO	18-Dec-20	Pallet	5320
0.6	34690D00AO	26-Dec-20	Pallet	5320
9	34690D00AO	14-Oct-20	Jumbo	5323
72	M21001B	17-Oct-20	Pallet	5323
100	M21109B	25-Nov-20	Jumbo	5323
66	34690D00AO	25-Nov-20	Pallet	5323
-9	34690D00AO	14-Dec-20	Pallet	5323
-20	M26118	15-Dec-20	Jumbo	5323
9.6	34690D01AO	18-Dec-20	Pallet	5323
9.08	06442122CA	26-Dec-20	Pallet	5323
9	34690D00AO	01-Oct-20	Pallet	5325
16.2	34690D01AO	22-Oct-20	Pallet	5325
60	M21112	23-Nov-20	Jumbo	5325
252	M26118	14-Dec-20	Jumbo	5325
130.2	34690D01AO	14-Dec-20	Pallet	5325
-60	M26118	21-Dec-20	Jumbo	5325
-16.2	34690D01AO	22-Dec-20	Pallet	5325
16.2	34690D01AO	22-Dec-20	Pallet	5325
100	M21112	15-Oct-20	Jumbo	5328
47.4	34690D00AO	26-Oct-20	Pallet	5328
-47.4	34690D00AO	26-Nov-20	Pallet	5328
-100	M26118	17-Dec-20	Jumbo	5328
24	34690D01AO	21-Dec-20	Pallet	5328
176	M21109B	26-Dec-20	Jumbo	5328
47.4	34690D00AO	26-Dec-20	Pallet	5328

Here's the sample intermediate calculation for calculating inventory levels on certain days (didn't include all days, because too many rows):

Plant	Jumbo	Pallet	Date
5320	136.85	0	18-Oct
5320	136.85	66	25-Oct
5320	142.85	66	25-Nov
5320	122.85	66	16-Dec
5320	122.85	15.2	18-Dec
5320	122.85	15.8	26-Dec
5323	9	0	14-Oct
5323	9	72	17-Oct
5323	109	138	25-Nov
5323	109	129	14-Dec
5323	89	129	15-Dec
5323	89	138.6	18-Dec
5323	89	147.68	26-Dec
5325	0	9	1-Oct
5325	0	25.2	22-Oct
5325	60	25.2	23-Nov
5325	312	155.4	14-Dec
5325	252	155.4	21-Dec
5325	252	155.4	22-Dec
5328	100	0	15-Oct
5328	100	47.4	26-Oct
5328	100	0	26-Nov
5328	0	0	17-Dec
5328	0	71.4	21-Dec
5328	176	118.8	26-Dec

Here's the desired result:

	Plant	Total Jumbo	Total Pallet	Total	% Jumbo	% Pallet
Oct	5320	1915.9	462	2377.9	80.6%	19.4%
Nov	5320	4141.5	1980	6121.5	68%	32%
Dec	5320	138320	3494	141814.1	97.5%	2.5%
Oct	5323	279	1080	1359	20.5%	79.5%
Nov	5323	870	2556	3426	25.4%	74.6%
Dec	5323	2594	3566.48	6160.48	42.1%	57.9%
Oct	5325	0	324	324	0%	100%
Nov	5325	480	756	1236	39%	61%
Dec	5325	4476	2347.8	6823.8	65.6%	34.4%
Oct	5328	1700	284.4	1984.4	85.7%	14.3%
Nov	5328	3000	1185	4185	72%	28%
Dec	5328	1776	191.4	1967.4	90.3%	9.7%

Thank you!

[Anonymous]

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]

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]

@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]

Hi @Anonymous ,

It is all depending on your raw data.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Anonymous ,

You can add those attributes in the summary table. I don't suggest you use measure in this case. You need to create temporary table like above in every measure. This can make your measure look bloated and affect performance. Because every measure needs to compute this temporary table once.

Best Regards,

Dedmon Dai

[Anonymous]

@v-deddai1-msft 

Hi Dedmond,

that does work, but is there a way to do it in a measure? Because I also have other attributes, such as business classification and valuation class linked to each posting (was not included in sample data) and they're only in the posting table. I have slicers for those attributes and would like it to work on the jumbo/pallet calculation also, i.e. if I choose food as business classification on the slicer, I only want the jumbo/pallet classification done for the materials associated with food as business classification. Currently, when a new table is created, those slicers from the posting table won't work.

[v-deddai1-msft · Accepted solution]

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]

@v-deddai1-msft 

Hi Dedmon, that only takes into account the inventory levels on the last day of the month, not daily inventory levels. I have continued the thread in this post: https://community.powerbi.com/t5/Desktop/Measure-using-current-inventory-measure-response/m-p/157440... as I'm not able to reply with anything besides a text or link.

[v-deddai1-msft]

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

[amitchandak]

@Anonymous , not very clear, But try the calculation like

CALCULATE(
SUM('Table'[Quantity]),
FILTER(
ALLSELECTED('Date Table'),
'Date Table'[Date] <= max('Date Table'[Date])
)
)