topic Re: Start of Week in DAX Commands and Tips

Start of Week

TJK — Wed, 13 Mar 2024 19:55:22 GMT

I have DAX caluclations that use the STARTOFMONTH function to determine the difference between a date and the start of the month date. But I don't know how to replicate it for the start of the week since STARTOFWEEK is not a function.

Here is my logic for start of month:

Loans = DISTINCTCOUNT(Opportunity[Id])

Running Total Loans =

CALCULATE(

[Loans],

FILTER(

VALUES('Opportunity History By Day'),

'Opportunity History By Day'[Date]<=MAX('Opportunity History By Day'[Date])))

Running Total Start of Month Loans = CALCULATE([Running Total Loans],STARTOFMONTH('Opportunity History By Day Date'[Date]))

Running Total Start of Month Loans Variance = [Running Total Loans]-[Running Total Start of Month Loans]

Is this possible to achieve for the week?

Re: Start of Week

lbendlin — Wed, 13 Mar 2024 22:19:28 GMT

First you need to define what you mean by "week". The definition of that varies widely across the globe.

Re: Start of Week

Anonymous — Thu, 14 Mar 2024 05:45:10 GMT

Hi TJK ,

lbendlin is right, we all have different definitions of "week".

If your definition of week is from Monday to Sunday, then you can use the following expression to get the week start date:

Week Start Date = Data[Date] - WEEKDAY(Data[Date],2) + 1

where the second parameter in the WEEKDAY function represents the week start date, you can adjust it according to your needs, for more information, please refer to the documentation: WEEKDAY function (DAX) - DAX | Microsoft Learn.

For more details on setting the week start date, please refer to the link: Calculating Week Start Date with DAX in Power BI - BI Elite.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Start of Week

TJK — Thu, 14 Mar 2024 12:01:42 GMT

Hello, thanks for the response.

Is there anyway to use the weekday function in a measure, not a calculated column?

For example, I can do STARTOFMONTH([date]) as a measure.

I cannot however do WEEKDAY([date],1) as a measure.

How would I use WEEKDAY in a measure to get the first day of the week?

Re: Start of Week

lbendlin — Thu, 14 Mar 2024 12:51:57 GMT

How would I use WEEKDAY in a measure to get the first day of the week?

of which week? Measures operate in filter context. What is your filter context?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Start of Week

TJK — Thu, 14 Mar 2024 12:55:46 GMT

When I use STARTOFMONTH, the measure simply takes the start of the month of the very first month in my dataset if I don't have any filter context applied.

I would like STARTOFWEEK to behave the same way.

Re: Start of Week

FreemanZ — Fri, 15 Mar 2024 05:22:32 GMT

there is no such DAX function.

if you could rephrase your case well and provide solid sample data with expected result, there is always a way.

Re: Start of Week

Anonymous — Mon, 18 Mar 2024 08:45:27 GMT

Hi TJK ,

If you want to get the week start date in the current context, just add the column name plus the max function to get the current value, but as Ibendlin said, more information is needed to better help you solve the problem.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Start of Week

lkarak — Fri, 05 Sep 2025 22:43:43 GMT

Thanks, this worked perfectly for me! I used it in the Calendar table of Power Pivot