Start of Week

[TJK]

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?

[Anonymous]

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.

[TJK]

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?

[Anonymous]

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.

[lbendlin]

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-...

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/1447...

[TJK]

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.

[FreemanZ]

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. 

[lbendlin]

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