# How to archive the WTD(week to today) by DAX? • ### 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

• 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
http://brentgreenwood.blogspot.com

Sunday, May 27, 2012 10:32 PM
• 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
http://brentgreenwood.blogspot.com

Sunday, May 27, 2012 10:56 PM

### 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
http://brentgreenwood.blogspot.com

Sunday, May 27, 2012 10:32 PM
• 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
• 