Doing ENDOFWEEK like ENDOFMONTH

[KPMEE]

Hi everyone,

I've got the following measure to get the data for the sum at the end of month day:

Value on last day of month of max selected date = 
CALCULATE (
    [Value Sum],
    ENDOFMONTH ('Date'[Date])
)

How can I do the same for the end of a week, as there's no built-in function for that?

[123abc]

To calculate the sum at the end of a week, similar to how you're calculating it at the end of a month, you can create a custom DAX measure using the FILTER function along with the WEEKNUM function. Here's a DAX measure that will give you the sum at the end of the selected week:

Value on last day of week of max selected date =

VAR MaxSelectedDate = MAX('Date'[Date])

VAR MaxSelectedWeek = WEEKNUM(MaxSelectedDate)

RETURN

CALCULATE (

[Value Sum],

FILTER (

ALL('Date'),

WEEKNUM('Date'[Date]) = MaxSelectedWeek ) )

In this DAX measure:

1. We first define the MaxSelectedDate variable to store the maximum selected date.

2. Then, we calculate the MaxSelectedWeek variable using the WEEKNUM function to get the week number of the maximum selected date.

3. Finally, we use the FILTER function to filter the 'Date' table and select only the rows that belong to the week represented by MaxSelectedWeek. We use WEEKNUM('Date'[Date]) to determine the week number for each date in the 'Date' table and compare it to MaxSelectedWeek.

4. The CALCULATE function then calculates the sum of the 'Value Sum' measure for the filtered rows, giving you the sum at the end of the selected week.

Make sure to replace 'Date' and [Value Sum] with the actual names of your date table and measure, respectively, in your Power BI data model.

[KPMEE]

Hey! First of all, thanks for the help. Even though I don't get any errors, the only values that show up are 0 or nothing.