Data Massaging help needed (pick record with max date + max sequence along with periodic sum of val)

[Anonymous]

Here is a typical requirement for Data massaging. 

Below is the structure of source data

Material	Group	Unit	Warehouse	Transaction_Date	Sequence	R/I/A	Quantity	Transfer (Y/N)	Qnt_post_trn	Rate
ItemE	GroupA	kgs	WH1	09-Feb-2012 10:30	1	Receipt	20000	N	20000	81.444
ItemD	GroupB	kgs	WH1	13-Feb-2012 10:30	1	Receipt	20000	N	20000	146.9936
ItemA	GroupB	kgs	WH2	15-Feb-2012 11:16	1	Receipt	6000	N	6000	170
ItemE	GroupA	kgs	WH1	15-Mar-2012 06:24	1	Issue	7000	Y	13000	90.7914
ItemE	GroupA	kgs	WH2	15-Mar-2012 06:24	1	Receipt	7000	Y	7000	90.7914
ItemD	GroupB	kgs	WH1	16-Mar-2012 06:12	1	Issue	2000	Y	18000	156.3067
ItemD	GroupB	kgs	WH2	16-Mar-2012 06:12	1	Receipt	2000	Y	2000	156.3067
ItemE	GroupA	kgs	WH2	19-Mar-2012 12:01	1	Issue	200	N	6800	90.7914
ItemA	GroupB	kgs	WH2	27-Mar-2012 08:43	1	Issue	6000	Y	0	170
ItemA	GroupB	kgs	WH1	27-Mar-2012 08:43	1	Receipt	6000	Y	6000	170
ItemD	GroupB	kgs	WH1	05-Apr-2012 05:37	1	Issue	2000	Y	16000	156.3067
ItemD	GroupB	kgs	WH2	05-Apr-2012 05:37	1	Receipt	2000	Y	4000	156.3067
ItemE	GroupA	kgs	WH1	14-Apr-2012 05:11	1	Issue	201	N	12799	90.7914
ItemE	GroupA	kgs	WH2	16-Apr-2012 11:18	1	Issue	1222	N	5578	90.7914
ItemD	GroupB	kgs	WH2	16-Apr-2012 11:18	1	Issue	1003	N	2997	156.3067
ItemD	GroupB	kgs	WH2	16-Apr-2012 11:18	2	Issue	300	N	2697	156.3067

This is table for material transactions in warehouses. I need to plot Monthly chart of total receipts, total issues and closing stock with following rules.  

1) Chart for selected material Group which would contain multiple materials to show columns for Issues and receipts for the period and line for closing stock for the period i.e. Month-Year. If a specific material selected, chart to represent only for that material and not the whole group. The chart to have drilling down to the dates of months for clicked month-year. 

2) Issues and receipts shall not cover internal transfers. Rest shall be summed up for month-Year.   

3) Closing stock is sum of stocks (Qnt_post_trn) of same material in various warehouses as of latest transaction in individual warehouse.

4) Multiple transactions for same material in same warehouse at same time can happen. Latest is the one with max Sequence of latest transaction datetime. 

I have transformed the table at source itself like below. 

Material	Group	Unit	Warehouse	Transaction_Date	Sequence	R/I/A	Quantity	Transfer (Y/N)	Qnt_post_trn	Rate	Receipt_kg	Receipt_Amt	Issue_kg	Issue_Amt	Adjustment_kg	Adjustment_Amt	Stock_kg	Stock_Amt
ItemE	GroupA	kgs	WH1	09-Feb-2012 10:30	1	Receipt	20000	N	20000	81.444	20000	1628880	0	0	0	0	20000	1628880
ItemD	GroupB	kgs	WH1	13-Feb-2012 10:30	1	Receipt	20000	N	20000	146.9936	20000	2939872	0	0	0	0	20000	2939872
ItemA	GroupB	kgs	WH2	15-Feb-2012 11:16	1	Receipt	6000	N	6000	170	6000	1020000	0	0	0	0	6000	1020000
ItemE	GroupA	kgs	WH1	15-Mar-2012 06:24	1	Issue	7000	Y	13000	90.7914	0	0	0	0	0	0	13000	1180288.2
ItemE	GroupA	kgs	WH2	15-Mar-2012 06:24	1	Receipt	7000	Y	7000	90.7914	0	0	0	0	0	0	7000	635539.8
ItemD	GroupB	kgs	WH1	16-Mar-2012 06:12	1	Issue	2000	Y	18000	156.3067	0	0	0	0	0	0	18000	2813520.6
ItemD	GroupB	kgs	WH2	16-Mar-2012 06:12	1	Receipt	2000	Y	2000	156.3067	0	0	0	0	0	0	2000	312613.4
ItemE	GroupA	kgs	WH2	19-Mar-2012 12:01	1	Issue	200	N	6800	90.7914	0	0	200	18158.28	0	0	6800	617381.52
ItemA	GroupB	kgs	WH2	27-Mar-2012 08:43	1	Issue	6000	Y	0	170	0	0	0	0	0	0	0	0
ItemA	GroupB	kgs	WH1	27-Mar-2012 08:43	1	Receipt	6000	Y	6000	170	0	0	0	0	0	0	6000	1020000
ItemD	GroupB	kgs	WH1	05-Apr-2012 05:37	1	Issue	2000	Y	16000	156.3067	0	0	0	0	0	0	16000	2500907.2
ItemD	GroupB	kgs	WH2	05-Apr-2012 05:37	1	Receipt	2000	Y	4000	156.3067	0	0	0	0	0	0	4000	625226.8
ItemE	GroupA	kgs	WH1	14-Apr-2012 05:11	1	Issue	201	N	12799	90.7914	0	0	201	18249.0714	0	0	12799	1162039.129
ItemE	GroupA	kgs	WH2	16-Apr-2012 11:18	1	Issue	1222	N	5578	90.7914	0	0	1222	110947.0908	0	0	5578	506434.4292
ItemD	GroupB	kgs	WH2	16-Apr-2012 11:18	1	Issue	1003	N	2997	156.3067	0	0	1003	156775.6201	0	0	2997	468451.1799
ItemD	GroupB	kgs	WH2	16-Apr-2012 11:18	2	Issue	300	N	2697	156.3067	0	0	300	46892.01	0	0	2697	421559.1699

Now I am able to calculate issues and receipts correctly as well as in dynamic manner. But the trouble is to pickup the closing stock dynamically for each month-year with rule 3 and 4 mentioned earlier. (That too required to be correct when drilled-down to days)

I understand that this is possible with DAX formula used to create a measure but not able to prepare the right one. 

Could anyone please help?

[v-eachen-msft]

Hi @Anonymous ,

According to your requirements, you could refer to the following measure.

Measure =
VAR a =
    ALLEXCEPT ( 'Table', 'Table'[Material], 'Table'[Warehouse] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qnt_post_trn] ),
        FILTER (
            'Table',
            'Table'[Sequence] = CALCULATE ( MAX ( 'Table'[Sequence] ), a )
                && 'Table'[Transaction_Date] = CALCULATE ( MAX ( 'Table'[Transaction_Date] ), a )
        )
    )

Here is mt test result.

[Anonymous]

This is not correct. 

Closing Stock for a period is the stock after last transaction in all warehouses put together. 

In your workout, only stock of the warehouse with latest transaction is considered.