Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to Lag/shift a column of Data

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:

 

CategoryBrandDateUnit ActualsForecastU-Lag1U-lag 2
CatsFlovo1/1/2015412   
CatsFlovo2/1/2015937 412 
CatsFlovo3/1/2015423 937412
CatsFlovo4/1/2015318 423937
CatsFlovo5/1/2015301 318423
CatsFlovo6/1/2015822 301318
CatsFlovo7/1/2015510 822301
CatsFlovo8/1/2015524 510822
CatsFlovo9/1/2015495 524510
CatsFlovo10/1/2015430 495524
CatsFlovo11/1/2015389 430495
CatsFlovo12/1/2015489 389430
CatsFlovo1/1/2016248 489389
CatsFlovo2/1/2016374 248489
CatsFlovo3/1/2016424 374248
CatsFlovo4/1/2016415 424374
CatsFlovo5/1/2016339 415424
CatsFlovo6/1/2016247 339415
CatsFlovo7/1/2016707 247339
CatsFlovo8/1/2016444 707247
CatsFlovo9/1/2016594 444707
CatsFlovo10/1/2016941 594444
CatsFlovo11/1/2016796 941594
CatsFlovo12/1/2016486 796941
CatsFlovo1/1/2017189 486796
CatsFlovo2/1/2017131 189486
CatsFlovo3/1/2017753 131189
CatsFlovo4/1/2017458 753131
CatsFlovo5/1/2017489 458753
CatsFlovo6/1/2017821 489458
CatsFlovo7/1/2017698 821489
CatsFlovo8/1/2017913 698821
CatsFlovo9/1/2017933 913698
CatsFlovo10/1/2017776 933913
CatsFlovo11/1/2017477 776933
CatsFlovo12/1/2017644 477776
DogsMeano1/1/2015343 644477
DogsMeano2/1/2015640 343644
DogsMeano3/1/2015537 640343
DogsMeano4/1/2015863 537640
DogsMeano5/1/2015544 863537
DogsMeano6/1/2015380 544863
DogsMeano7/1/2015779 380544
DogsMeano8/1/2015370 779380
DogsMeano9/1/2015953 370779
DogsMeano10/1/2015344 953370
DogsMeano11/1/2015370 344953
DogsMeano12/1/2015869 370344
DogsMeano1/1/2016676 869370
DogsMeano2/1/2016612 676869
DogsMeano3/1/2016460 612676
DogsMeano4/1/2016488 460612
DogsMeano5/1/2016411 488460
DogsMeano6/1/2016385 411488
DogsMeano7/1/2016601 385411
DogsMeano8/1/2016975 601385
DogsMeano9/1/2016486 975601
DogsMeano10/1/2016885 486975
DogsMeano11/1/2016555 885486
DogsMeano12/1/2016261 555885
DogsMeano1/1/2017604 261555
DogsMeano2/1/2017828 604261
DogsMeano3/1/2017573 828604
DogsMeano4/1/2017608 573828
DogsMeano5/1/2017245 608573
DogsMeano6/1/2017483 245608
DogsMeano7/1/2017100 483245
DogsMeano8/1/2017744 100483
DogsMeano9/1/2017806 744100
DogsMeano10/1/2017146 806744
DogsMeano11/1/2017860 146806
DogsMeano12/1/2017379 860146
CatsFlovo6/1/2018457165379860
DogsMeano6/1/2018476820457379
CatsFlovo5/1/2018686791476457
DogsMeano5/1/2018643266686476
CatsFlovo4/1/2018352732643686
DogsMeano4/1/2018861492352643
CatsFlovo2/1/2018597874861352
DogsMeano2/1/2018855745597861
CatsFlovo3/1/2018939665855597
DogsMeano3/1/2018269600939855
CatsFlovo1/1/2018425794269939
DogsMeano1/1/2018478208425269
 *Category Cont?Brand Cont?    478425
      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. 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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
Not applicable

Hi Jimmy,

 

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

Anonymous
Not applicable


@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

 


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.