topic Re: Understanding EOMONTH in DAX in DAX Commands and Tips

Understanding EOMONTH in DAX

Ackbar-Learner — Sat, 04 Feb 2023 02:55:24 GMT

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

Re: Understanding EOMONTH in DAX

Jihwan_Kim — Sat, 04 Feb 2023 03:35:01 GMT

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

Re: Understanding EOMONTH in DAX

andrewpirie — Sat, 04 Feb 2023 03:43:54 GMT

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

Re: Understanding EOMONTH in DAX

Ackbar-Learner — Sat, 04 Feb 2023 07:35:55 GMT

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