Understanding EOMONTH in DAX

[Ackbar-Learner]

Hi, I am having a very strange situation with EOMONTH

Below is the measure I am using to get last day of previous month of either the selected date or today's date whichever comes first:

Board01fActualizedDateSelected = MIN(EOMONTH(TODAY(),-1), EOMONTH([Board01aLastDateSelected],-1))

 and this is giving me back this result, which is fine:

Now using above measure, I want to calculate the amounts before or equal to the above date. So i used the following measure:

Board02bP&LAllTransactionAmountwithSignActual = 
VAR Result = sumx(GLChartOfAccounts,
        CALCULATE([P&LAllTransactionAmountEUR],
        FILTER(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000),
        FILTER(BudgetCalendar,BudgetCalendar[Date]<=[Board01fActualizedDateSelected]))
        *GLChartOfAccounts[Sign])
RETURN
    Result

and the above is returning me Blank, which is not correct.

So i tweaked the above measure by just replacing it with the actual date of 31/01/2023 as below:

Board02bP&LAllTransactionAmountwithSignActual = 
VAR Result = sumx(GLChartOfAccounts,
        CALCULATE([P&LAllTransactionAmountEUR],
        FILTER(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000),
        FILTER(BudgetCalendar,BudgetCalendar[Date]<=date(2023,1,31)))
        *GLChartOfAccounts[Sign])
RETURN
    Result

and it works fine with below result:

What should i correct to get the EOMONTH measure fine so that it works properly on the second measure because obviously hardcoding a date in a measure is not the way to go.

Thanks for any guidance

[andrewpirie]

Am I correct that Board01aLastDateSelected measure doesn't rely on a specific GLChartOfAccounts? If so, the measure may be being affected by the context transition of the GLChartOfAccounts row being iterated over in SUMX.

Something to try is moving that measure reference to a variable outside of that expression and referring to that variable within the FILTER statement so that it is evaluated outside of the currently iterated row context, like below

Board02bP&LAllTransactionAmountwithSignActual = 

VAR _actualizedDateSelected = [Board01fActualizedDateSelected]

VAR Result = sumx(GLChartOfAccounts,
        CALCULATE([P&LAllTransactionAmountEUR],
        FILTER(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000),
        FILTER(BudgetCalendar,BudgetCalendar[Date]<= _actualizedDateSelected ))
        *GLChartOfAccounts[Sign])
RETURN
    Result

[Ackbar-Learner]

It is the same as the first solution proposed. You were both right!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

Board02bP&LAllTransactionAmountwithSignActual =
VAR _Board01fActualizedDateSelected = [Board01fActualizedDateSelected]
VAR Result =
    SUMX (
        GLChartOfAccounts,
        CALCULATE (
            [P&LAllTransactionAmountEUR],
            FILTER (
                ALL ( GLAccountClientUser[GLAccountValue] ),
                GLAccountClientUser[GLAccountValue] < 4700000
            ),
            FILTER (
                BudgetCalendar,
                BudgetCalendar[Date] <= _Board01fActualizedDateSelected
            )
        ) * GLChartOfAccounts[Sign]
    )
RETURN
    Result