Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
Hi Jimmy,
Thanks for your response! I appreciate your help! I should be able to apply this logic in the future!
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |