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