cancel
Showing results for
Did you mean:
Helper I

## Measure to calculate based on previous data point.

Hello, I need to multiply to parameters. One of which is sampled every 3 days and I need to use the previse sampled point for today calculation until a new sample point arrive, (See screen clip). So for day 02/01/17 calculate 20*1000.

The calculation is:

Product of day parameters = calculate(PRODUCTX(Table2,Table2[PARA1]*Table2[Sample]),Table2[Day])

I thought that EARLIER should provide the answer but could not get is to work.

Thanks

Eyal

1 ACCEPTED SOLUTION
Community Champion

Hi @Eyal

Following DAX formula will do the job

```Product Of Day Parameters=
VAR PreviousSample =
IF (
ISBLANK ( Table1[Sample] ),
CALCULATE (
LASTNONBLANK ( Table1[Sample], Table1[Sample] ),
FILTER ( Table1, Table1[Day] < EARLIER ( Table1[Day] ) )
),
Table1[Sample]
)
RETURN
Table1[PARA2] * PreviousSample```

Regards,
Zubair

4 REPLIES 4
Super User III

Hi,

Try this

=calculate(PRODUCTX(Table2,Table2[PARA1]*IF(ISBLANK(Table2[Sample]),CALCULATE(SUM(Table2[Sample]),PREVIOUSDAY(Table2[Day])),Table2[Sample])),Table2[Day])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

HI Ashish_Mathur

tried your solution but did not get the correct result..

Thanks any way/

Eyal

Community Champion

Hi @Eyal

Following DAX formula will do the job

```Product Of Day Parameters=
VAR PreviousSample =
IF (
ISBLANK ( Table1[Sample] ),
CALCULATE (
LASTNONBLANK ( Table1[Sample], Table1[Sample] ),
FILTER ( Table1, Table1[Day] < EARLIER ( Table1[Day] ) )
),
Table1[Sample]
)
RETURN
Table1[PARA2] * PreviousSample```

Regards,
Zubair

Helper I

thanks a lot work like a charm.

Eyal

Announcements