Measure based on IF

[ConstMoss]

hi all - i have a table with the following (sample) data. I need to create a measure based on the ID of the column in the table.

1. If the ID of the column doesn't include "hist", i want to sum of all the values up until the selected date.

2. If the ID of the column does include "hist", i want to grab the last nonempty value.

I have the measure as shown below which accounts for sceanrio #1, not sure how to add an "if" to make it work for both scenarios above (#1 and #2)?

EndingAmount =
CALCULATE (
SUM (Table[Amount]),
FILTER (
ALL ( 'dimDate'[Date] ),
'dimDate'[Date] <= MAX ( 'dimDate'[Date] )
)
)

ID	Date	Amount
123	1/1/2018	10
124	1/2/2018	11
125	1/3/2018	12
126	1/4/2018	13
127	1/5/2018	14
hist-123	1/1/2017	20
hist-124	1/2/2017	21
hist-125	1/3/2017	22
hist-126	1/4/2017	23
hist-127	1/5/2017	24

[v-yuezhe-msft · Accepted solution]

@ConstMoss,

Create the following column and measure in your table, then check if you get expected result.

Column = IF(
	ISERROR(
		SEARCH("hist", Table[ID])
	),
	1,
	0
)

EndingAmount = IF(FIRSTNONBLANK(Table[Column],1)=1,
CALCULATE(SUM(Table[Amount]),FILTER(ALL(Table),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table[Column])),LASTNONBLANK(Table[Amount],1))

Regards,
Lydia