If you look around the DAX functions, you are going to be disappointed. There is no week-to-date function like there is in Tableau. There are different methods you can google, but let’s create our one here from scratch.
In this example, we are using the FactOnlineSales table from the Contoso Retail DW.
Before we start, want can as an option bring the old data into the future, by using the method outlined here. This is not a requirement, but it means we can create live-looking scenarios with sales data up until yesterday.
To verify the measure we are going to create, we can create a fixed date range measure that we can compare our dynamic measure to:OnlineSales WTD check = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], DATE(2023, 12, 11), DATE(2023, 12, 15)))
This measure uses the DATESBETWEEN() function as the filter for the CALCULATE() function. Note, that sales will be inclusive from the start to the end dates. You can change the date range to reflect the current week you are in. The dates used in this example are reflective of the time when I created the function.
To create a dynamic measure, we can start by adding a new measure OnlineSales WTD (week to date).
Let’s start by adding in the TODAY() function
OnlineSales WTD = TODAY()
-1
We are going to use the TODAY()-1 as we usually want the data up until the end of the previous day and not to show any partial data for the current day that may be imported to the database.
If we add it to a card visual, we can verify the output as today-1 (at the time of writing):
Next, we can use the WEEKDAY function to get the day number from today. We also use variables here, to tidy up the code:OnlineSales WTD =
VAR ThisDay = TODAY()-1
VAR ThisDayNum = WEEKDAY(currentday)
RETURN
currentdayNum
The measure now displays 6. Today is the 16th of November 2023 (at the time of writing), which is a Saturday, but we added the -1, which is Friday, so we want it to display 5. To get the WEEKDAY() function to start on a Monday, we can add 2 as the second argument, which sets the first day of the week (1) to Monday and the last day of the week to Sunday (7). You can change this according to your needs.OnlineSales WTD =
ThisDayNum
VAR ThisDay = TODAY()-1
VAR ThisDayNum = WEEKDAY(currentday, 2)
RETURN
This now gives us 5, which is what we want:
Next, we want to calculate the first day of the week, which we can do as follows:
OnlineSales WTD =
VAR ThisDay = TODAY()-1
VAR ThisDayNum = WEEKDAY(currentday, 2)VAR FirstDayOfWeek = thisday -(thisdayNum-1)
FirstDayOfWeek
RETURN
So now we have the logic for calculating the first day of the week and today. So we want to calculate the sales between these 2 dates. So let’s try and use this logic to create a dynamic WTD measure:
OnlineSales WTD =
VAR ThisDay = TODAY()-1
VAR ThisDayNum = WEEKDAY(Thisday,2)
VAR FirstDayOfWeek = Thisday -(ThisDayNum-1)
VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay))
VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay))
RETURN
IF(ThisDayNum=1, MonSales,
IF(ThisDayNum>1, WTDSales))
The code uses two different calculations for WTD. If the previous day is a Monday, it just calculates the Monday sales, if it is after Monday, it calculates the WTD sales
Now let’s say we want to go back to any point in time and get the Week to Date sales, we can tweak the code as follows:OnlineSales WTD =
VAR ThisDay = MAX('Date'[Date])-1
VAR ThisDayNum = WEEKDAY(Thisday,2)
VAR FirstDayOfWeek = Thisday -(ThisDayNum-1)
VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay))
VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay))
RETURN
IF(ThisDayNum=1, MonSales,
IF(ThisDayNum>1, WTDSales))
Now if we add the day filter and select today’s date, the week-to-date measure should still match the check, but we can also go back to any point in time and calculate week-to-date.
Lastly, to calculate the previous year week to date sales, we can create a measure based on our existing OnlineSales WTD measure, but use the SAMEPERIODLASTYEAR() function, to calculate the previous year’s week-to-date, without having to re-rewrite all the code.OnlineSales WTD Prev Year = CALCULATE([OnlineSales WTD], SAMEPERIODLASTYEAR('Date'[Date]))