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.
I am trying to optimize this lagging measure. The following works, and gives me the desired outcome. Basically, if the month interval is 12 (December), return the first filter. If not, return the second. The basis of the measure is to look at the selected month (determined in a slicer), and then return the values for the next month.
NextMonthJobsStartingTest =
IF (
SELECTEDVALUE ( 'Calendar'[MonthInt] ) = 12,
CALCULATE (
[SUMTotalAmt],
FILTER (
Sales,
Sales[ProjectedStartDateMonthInt] = 1
&& Sales[ProjectedStartDateYear]
= SELECTEDVALUE ( 'Calendar'[Year] ) + 1
)
),
CALCULATE (
[SUMTotalAmt],
FILTER (
Sales,
Sales[ProjectedStartDateMonthInt]
= SELECTEDVALUE ( 'Calendar'[MonthInt] ) + 1
&& Sales[ProjectedStartDateYear] = SELECTEDVALUE ( 'Calendar'[Year] )
)
)
)
I'm trying to parse this down and make it more efficient with the following.
NextMonthJobsStartingTest2 =
CALCULATE (
[SUMTotalAmt],
IF (
SELECTEDVALUE ( 'Calendar'[MonthInt] ) = 12,
FILTER (
Sales,
Sales[ProjectedStartDateMonthInt] = 1
&& Sales[ProjectedStartDateYear]
= SELECTEDVALUE ( 'Calendar'[Year] ) + 1
),
FILTER (
Sales,
Sales[ProjectedStartDateMonthInt]
= SELECTEDVALUE ( 'Calendar'[MonthInt] ) + 1
&& Sales[ProjectedStartDateYear] = SELECTEDVALUE ( 'Calendar'[Year] )
)
)
)
I recieve the following error message:
It appears that the measure doesn't like using IF() to determine which filter to select... What other options can I use? Also, is there a better way to trim down this measure?
TIA
Solved! Go to Solution.
Hi @Anonymous
You need to determin the next month period, try this way, assume you have Date column in Calendar
NextMonthJobsStartingTest =
VAR CurDate = DATE(SELECTEDVALUE ( 'Calendar'[Year] ),SELECTEDVALUE( 'Calendar'[MonthInt] ),1)
VAR MinDate = EDATE(CurDate,1)
VAR MaxDate = EDATE(CurDate,2)
RETURN
SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]>=MinDate&&'Calendar'[Date]<MaxDate),[SUMTotalAmt])
Hi @Anonymous
You need to determin the next month period, try this way, assume you have Date column in Calendar
NextMonthJobsStartingTest =
VAR CurDate = DATE(SELECTEDVALUE ( 'Calendar'[Year] ),SELECTEDVALUE( 'Calendar'[MonthInt] ),1)
VAR MinDate = EDATE(CurDate,1)
VAR MaxDate = EDATE(CurDate,2)
RETURN
SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]>=MinDate&&'Calendar'[Date]<MaxDate),[SUMTotalAmt])
@Vera_33. Thanks so much. This did work. The only change I added was to use 'Sales'[ProjectedStartDate] in place of 'Calendar'[Date]. I need to filter based on the Projected Start Date...
As well, I had to drop the All('Calendar') piece of the filter. Any idea why that would be?
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |