cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConstMoss Regular Visitor
Regular Visitor

Measure based on IF

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] )
)
)

 

IDDateAmount
1231/1/201810
1241/2/201811
1251/3/201812
1261/4/201813
1271/5/201814
hist-1231/1/201720
hist-1241/2/201721
hist-1251/3/201722
hist-1261/4/201723
hist-1271/5/201724
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Measure based on IF

@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))

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Measure based on IF

@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))

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.