locked
Dax equivalent to MDX last periods function RRS feed

  • Question

  • I am trying to create a calculation similar to MDX "Last periods" function because i need a calculation for the last 4 weeks. I know I can do a dates between and subtract 28 days but that will not work because if you are in the middle of a week then I only want to go back 3 full weeks plus the current week you are in.

    Is there any way to do date math on the week level? If so does anyone have examples on how to set this up?

    Thursday, November 15, 2012 2:18 PM

Answers

  • Ok, if I understand you correctly, you cant subtract 28 days because sometimes you are in the middle of the week. So what you need to do is to find the first day of the week always, and then substract 21 days (so that will go to the first day 4 weeks back - incl current week). try the formula below

    =calculate(min('Date'[DateKey]),filter('Date',EARLIER('Date'[WeekOfYear])= 'Date'[WeekOfYear] )) - 21


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Proposed as answer by Elvis Long Tuesday, November 20, 2012 6:51 AM
    • Marked as answer by Elvis Long Monday, November 26, 2012 12:55 AM
    Thursday, November 15, 2012 2:58 PM
    Answerer