How to Lag/shift a column of Data

[Anonymous]

Hello,

I have a question regarding lagging formulas in dax. I know there are number of diffferent time intellligence functions as well as filters that can accomplish lagging, but I am looking for the most effecient(performance wise) way to accomphlish lags is dax. Here is an example of what I am looking to do:

Category	Brand	Date	Unit Actuals	Forecast	U-Lag1	U-lag 2
Cats	Flovo	1/1/2015	412
Cats	Flovo	2/1/2015	937		412
Cats	Flovo	3/1/2015	423		937	412
Cats	Flovo	4/1/2015	318		423	937
Cats	Flovo	5/1/2015	301		318	423
Cats	Flovo	6/1/2015	822		301	318
Cats	Flovo	7/1/2015	510		822	301
Cats	Flovo	8/1/2015	524		510	822
Cats	Flovo	9/1/2015	495		524	510
Cats	Flovo	10/1/2015	430		495	524
Cats	Flovo	11/1/2015	389		430	495
Cats	Flovo	12/1/2015	489		389	430
Cats	Flovo	1/1/2016	248		489	389
Cats	Flovo	2/1/2016	374		248	489
Cats	Flovo	3/1/2016	424		374	248
Cats	Flovo	4/1/2016	415		424	374
Cats	Flovo	5/1/2016	339		415	424
Cats	Flovo	6/1/2016	247		339	415
Cats	Flovo	7/1/2016	707		247	339
Cats	Flovo	8/1/2016	444		707	247
Cats	Flovo	9/1/2016	594		444	707
Cats	Flovo	10/1/2016	941		594	444
Cats	Flovo	11/1/2016	796		941	594
Cats	Flovo	12/1/2016	486		796	941
Cats	Flovo	1/1/2017	189		486	796
Cats	Flovo	2/1/2017	131		189	486
Cats	Flovo	3/1/2017	753		131	189
Cats	Flovo	4/1/2017	458		753	131
Cats	Flovo	5/1/2017	489		458	753
Cats	Flovo	6/1/2017	821		489	458
Cats	Flovo	7/1/2017	698		821	489
Cats	Flovo	8/1/2017	913		698	821
Cats	Flovo	9/1/2017	933		913	698
Cats	Flovo	10/1/2017	776		933	913
Cats	Flovo	11/1/2017	477		776	933
Cats	Flovo	12/1/2017	644		477	776
Dogs	Meano	1/1/2015	343		644	477
Dogs	Meano	2/1/2015	640		343	644
Dogs	Meano	3/1/2015	537		640	343
Dogs	Meano	4/1/2015	863		537	640
Dogs	Meano	5/1/2015	544		863	537
Dogs	Meano	6/1/2015	380		544	863
Dogs	Meano	7/1/2015	779		380	544
Dogs	Meano	8/1/2015	370		779	380
Dogs	Meano	9/1/2015	953		370	779
Dogs	Meano	10/1/2015	344		953	370
Dogs	Meano	11/1/2015	370		344	953
Dogs	Meano	12/1/2015	869		370	344
Dogs	Meano	1/1/2016	676		869	370
Dogs	Meano	2/1/2016	612		676	869
Dogs	Meano	3/1/2016	460		612	676
Dogs	Meano	4/1/2016	488		460	612
Dogs	Meano	5/1/2016	411		488	460
Dogs	Meano	6/1/2016	385		411	488
Dogs	Meano	7/1/2016	601		385	411
Dogs	Meano	8/1/2016	975		601	385
Dogs	Meano	9/1/2016	486		975	601
Dogs	Meano	10/1/2016	885		486	975
Dogs	Meano	11/1/2016	555		885	486
Dogs	Meano	12/1/2016	261		555	885
Dogs	Meano	1/1/2017	604		261	555
Dogs	Meano	2/1/2017	828		604	261
Dogs	Meano	3/1/2017	573		828	604
Dogs	Meano	4/1/2017	608		573	828
Dogs	Meano	5/1/2017	245		608	573
Dogs	Meano	6/1/2017	483		245	608
Dogs	Meano	7/1/2017	100		483	245
Dogs	Meano	8/1/2017	744		100	483
Dogs	Meano	9/1/2017	806		744	100
Dogs	Meano	10/1/2017	146		806	744
Dogs	Meano	11/1/2017	860		146	806
Dogs	Meano	12/1/2017	379		860	146
Cats	Flovo	6/1/2018	457	165	379	860
Dogs	Meano	6/1/2018	476	820	457	379
Cats	Flovo	5/1/2018	686	791	476	457
Dogs	Meano	5/1/2018	643	266	686	476
Cats	Flovo	4/1/2018	352	732	643	686
Dogs	Meano	4/1/2018	861	492	352	643
Cats	Flovo	2/1/2018	597	874	861	352
Dogs	Meano	2/1/2018	855	745	597	861
Cats	Flovo	3/1/2018	939	665	855	597
Dogs	Meano	3/1/2018	269	600	939	855
Cats	Flovo	1/1/2018	425	794	269	939
Dogs	Meano	1/1/2018	478	208	425	269
*Category Cont?	Brand Cont?				478	425
						478

Questions:

1. How to lag the units by 1 month or 2 months(given by U-lag 1 and U lag 2)?Category.

2. Will the Catgeory and Brand automaticall fill down?

3. Aggreagated lags over a period.  For example this month is June so I would like to sum july 2018 to June 2017 and then lag total three months.

Hopefully, my question accurately describe what I am looking to solve.

Thank you in advance for your help. 

[v-yuta-msft · Accepted solution]

Hi ander651,

So your requirement is to achieve aggregation value based on several previous months in current month, right? As a general solution in DAX, you can refer to pattern like this:

Aggregation Value =
VAR Previous_N_Months_Start_Date =
    EDATE ( MAX ( Table1[Date] ), - N )
VAR Previous_N_Months_Last_Date =
    MAX ( Table1[Date] ) - 1
RETURN
    CALCULATE (
        aggregation,
        FILTER (
            ALLEXCEPT ( Table, Table1[Category] ),
            Table[Date] >= Previous_N_Months_Start_Date
                && Table[Date] <= Previous_N_Months_Last_Date
        )
    )

Hope it's helpful to you.

Jimmy Tao

[Anonymous]

Hi Jimmy,

Thanks for your response! I appreciate your help!  I should be able to apply this logic in the future!

[Anonymous]

---

@Anonymous wrote:

Hello,

I have a question regarding lagging formulas in dax. I know there are number of diffferent time intellligence functions as well as filters that can accomplish lagging, but I am looking for the most effecient(performance wise) way to accomphlish lags is dax.

-- Have you tried any function so far ? Do you face any performance issue with them ? please share the details.

Thanks
Raj

---