Isssue with Calculate with DAX

[Poplin]

I'm trying to aggregate planned sales after September 2024 on a monthly basis using the following DAX expression:

■DAX
SalesBP =

VAR BPMonth = [BPMonth]

RETURN CALCULATE('Measure'[Planned Sales], 'FY24Sales'[date] > BPMonth)

Where:

• [BPMonth] is created as a separate measure and represents “2024/8/1.”
• The calculation should include sales values in a matrix with columns representing the year and month.

However, I noticed that the SalesBP calculation includes data for April and May 2024, which is unexpected.

■Matrix Image 1:

               2024/4     2024/5

SalesBP   XXXXX      XXXXX

I’ve tried the following expressions, but the results remain the same:

1. RETURN = CALCULATE('Measure'[Planned Sales], KEEPFILTERS('FY24Sales'[date] > BPMonth))
2. RETURN = CALCULATE('Measure'[Planned Sales], FILTER('FY24Sales'[date] > BPMonth))

Interestingly, when I replace [BPMonth] with Date(2024,8,1), the matrix displays correctly:

■Matrix Image 2:

               2024/8     2024/9     2024/10     2024/11     2024/12   -----   2025/3

SalesBP   XXXXX      XXXXX      XXXXX       XXXXX       XXXXX    -----    XXXXX

I7m unsure of the cause and would appreciate any insights. 

[tharunkumarRTK]

@Poplin 

 I guess, because of your computer region settings, Power BI is assuming "2024/8/1" as Jan 8th 2024 instead of August 1st 2024. Try changing the value in your BPMonth measure to "2024/1/8" and see the result.

Date() function worked for your because, the sequence of parameters that date function accepts are Year, Month and Date and you passed them correctly.

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[Poplin]

Thank you for your reply.

I hadn't considered that the issue might be related to my computer's region settings.

Because my explanation may have been unclear, let me clarify:
The BPMonth measure is calculated as follows:

■ BPMonth measure
BPMonth = EDATE(DATEVALUE("[ActualDate]" & "/1" ))
※ ActualDate = "2024/5"

[Anonymous]

Hi @Poplin ,

It's the context that causes the problem, how [BPMonth] is defined as a date, and the results it returns when put into different visual objects may be different. If it's a dynamically customized value, I'd recommend that you create an unrelated schedule and get that date via selectvalue().

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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