Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
It is the same as the first solution proposed. You were both right!
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |