Forum Discussion

Narukkp's avatar
Narukkp
Icon for Helper V rankHelper V
6 years ago
Solved

Previous Row closing balance

Hi Team,

I required below highlighed format output. I mean previoiuse row closing balance should be shown in opening balance in combination of project code and item.

Need Help
  • Narukkp's avatar
    Narukkp
    6 years ago

    Hi 

    amitchandak i do not have any date field. Any how i got the solution my self and it is working as expected. 

     
    Concate = CONCATENATE(Test[ProjectCode],Test[Item])
    Row Number =
    VAR CurrentCustomer= Test[Concate]
    VAR CurrentDate = Test[MonthYear]
    RETURN

    CALCULATE(
    DISTINCTCOUNT(Test[Date]),
    FILTER(
    ALL( Test),
    Test[Concate]=CurrentCustomer
    && Test[MonthYear] <= CurrentDate
    )
    )
     
    PreviousSales1 =
    VAR CurrentCustomer= SELECTEDVALUE(Test[Concate])
    VAR Rno = SELECTEDVALUE(Test[Row Number])
    RETURN

    CALCULATE(
    SUM(Test[ClosingBalance]),
    FILTER(
    ALL( Test),
    Test[Concate] = CurrentCustomer
    && Test[Row Number] = Rno-1
    )
    )
     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Make sure you have date calendar. Use time intelligence
    Opening balance = CALCULATE(SUM(Table[closing balance]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    //OR
    Opening balance = CALCULATE(SUM(Table[closing balance]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

    • Narukkp's avatar
      Narukkp
      Icon for Helper V rankHelper V
      6 years ago

      Hi 

      amitchandak i do not have any date field. Any how i got the solution my self and it is working as expected. 

       
      Concate = CONCATENATE(Test[ProjectCode],Test[Item])
      Row Number =
      VAR CurrentCustomer= Test[Concate]
      VAR CurrentDate = Test[MonthYear]
      RETURN

      CALCULATE(
      DISTINCTCOUNT(Test[Date]),
      FILTER(
      ALL( Test),
      Test[Concate]=CurrentCustomer
      && Test[MonthYear] <= CurrentDate
      )
      )
       
      PreviousSales1 =
      VAR CurrentCustomer= SELECTEDVALUE(Test[Concate])
      VAR Rno = SELECTEDVALUE(Test[Row Number])
      RETURN

      CALCULATE(
      SUM(Test[ClosingBalance]),
      FILTER(
      ALL( Test),
      Test[Concate] = CurrentCustomer
      && Test[Row Number] = Rno-1
      )
      )