locked
How to archive the WTD(week to today) by DAX? RRS feed

  • Question

  • Hello Friends,

    IN DAX, there are built-in functions like TotalYTD, TotalQTD and TotalMTD. However, what if Iwanna archiev the fuction of WTD(Week to today)? How the DAX will look like ?

    Anyone knows?


    Derek

    Thursday, May 24, 2012 3:29 AM

Answers

  • Hi Derek -

    Here's one way to solve this one:

    1. Using a Date dimension separate from your transactions, calculate a week start date.  I used a linked table and in Excel used the following (=E2-WEEKDAY(E2,1)+1).

    2. Create a calculated measure with the following:

    =CALCULATE(sum('Sales'[SalesAmt])
                  ,DATESBETWEEN('date'[date]
                                ,LASTDATE('date'[WeekStartDate])
                                ,LASTDATE('date'[date])
                              )
              )

    Basically just using DATESBETWEEN to create the set of dates that the DATESMTD / DATESMTD / DATESYTD functions give you out of the box.

    Giving me the expected results below:

    Hope that helps.

    Brent


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Sunday, May 27, 2012 10:32 PM
    Answerer
  • Hi Derek -

    Another thought on this one.  Might be better to blank out the week-to-date running total at levels above the weekly level, instead of always showing latest week like above.  Was looking at this across months, and thinking blanks would be cleaner.  To do that, you can use some IF (VALUES) slickness like this:

    =IF(COUNTROWS(VALUES('date'[week]))=1
          ,CALCULATE(sum('Sales'[SalesAmt])
                         ,DATESBETWEEN('date'[date]
                                       ,LASTDATE('date'[WeekStartDate])
                                       ,LASTDATE('date'[date])
                                      ) 
                    )
         ,Blank()
       )

    Hope that helps.

    Brent


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, May 27, 2012 10:56 PM
    Answerer

All replies

  • Hi Derek -

    Here's one way to solve this one:

    1. Using a Date dimension separate from your transactions, calculate a week start date.  I used a linked table and in Excel used the following (=E2-WEEKDAY(E2,1)+1).

    2. Create a calculated measure with the following:

    =CALCULATE(sum('Sales'[SalesAmt])
                  ,DATESBETWEEN('date'[date]
                                ,LASTDATE('date'[WeekStartDate])
                                ,LASTDATE('date'[date])
                              )
              )

    Basically just using DATESBETWEEN to create the set of dates that the DATESMTD / DATESMTD / DATESYTD functions give you out of the box.

    Giving me the expected results below:

    Hope that helps.

    Brent


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Sunday, May 27, 2012 10:32 PM
    Answerer
  • Hi Derek -

    Another thought on this one.  Might be better to blank out the week-to-date running total at levels above the weekly level, instead of always showing latest week like above.  Was looking at this across months, and thinking blanks would be cleaner.  To do that, you can use some IF (VALUES) slickness like this:

    =IF(COUNTROWS(VALUES('date'[week]))=1
          ,CALCULATE(sum('Sales'[SalesAmt])
                         ,DATESBETWEEN('date'[date]
                                       ,LASTDATE('date'[WeekStartDate])
                                       ,LASTDATE('date'[date])
                                      ) 
                    )
         ,Blank()
       )

    Hope that helps.

    Brent


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, May 27, 2012 10:56 PM
    Answerer
  • Thank you, Sir.

    Derek

    Wednesday, May 30, 2012 5:58 PM