Forum Discussion

Eyal's avatar
Eyal
Icon for Helper II rankHelper II
8 years ago
Solved

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

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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


4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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


      • Eyal's avatar
        Eyal
        Icon for Helper II rankHelper II
        8 years ago

        Hi Zubair_Muhammad

         

        thanks a lot work like a charm.

         

        Eyal

    • Eyal's avatar
      Eyal
      Icon for Helper II rankHelper II
      8 years ago

      HI Ashish_Mathur

       

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

      Thanks any way/

      Eyal