cancel
Showing results for
Did you mean:
Highlighted
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] )
)
)

 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
1 ACCEPTED SOLUTION

Accepted Solutions
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))```

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.
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))```

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.