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 have two sets of formulas set up the same way, but one is not working and I cannot figure out why. In fact table SalesValueEntry I have two formulas:
SalesAmount =
SUM ( 'factSalesValueEntry'[SalesAmount_RCY] )
SalesAmountN-1 =
CALCULATE ( SUM ( 'factSalesValueEntry'[SalesAmount_RCY] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
There are both working and validated. In the customer table I have to measures related to the ones aboves:
CustomerTurnover =
SUMX ( RELATEDTABLE ( 'factSalesValueEntry' ), [SalesAmount_SVE] )
This one is working
CustomerTurnoverN-1=
SUMX ( RELATEDTABLE ( 'factSalesValueEntry' ), [SalesAmountN-1_SVE] )
This one is not and I have no clue why. It doesn't give me an error but just returns blank values. The setup looks a bit sketchy anyways on first sight, but I need this for a calculated colum in the customer table:
SWITCH ( TRUE (), [CustomerTurnover] > 0 && [CustomerTurnoverN-1] > 0, "ACTIVE", [CustomerTurnoverN-1] > 0 && [CustomerTurnover] <= 0, "LOST", [CustomerTurnoverN-1] <= 0 && [CustomerTurnover] > 0, "NEW", "N/A" )
@brentb- Check this out and let me know if this works. Try modifying the CustomerTurnoverN-1 measure to use an explicit filter on the date table
CustomerTurnoverN-1=
CALCULATE(
SUMX(RELATEDTABLE('factSalesValueEntry'), [SalesAmount_SVE]),
FILTER(
ALL('Date'),
'Date'[Date] IN DATEADD('Date'[Date], -1, YEAR)
)
)
I've hypothetically established this DAX measure, but without much clarity regarding your datasets. If this doesn't serve your needs, I recommend you share a sample dataset. This will give me a more comprehensive understanding of your data structure, enabling me to devise a more suitable solution. Cheers
Thank you for pointing me in the right direction. This worked eventually:
CALCULATE(
SUMX(RELATEDTABLE('factSalesValueEntry'), [SalesAmount_SVE]),
DATEADD('Date'[Date], -1, YEAR)
)
@brentb- Glad the solution worked for you. Could you please mark this as a solution and give it a 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 |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
47 | |
46 | |
20 | |
16 |