Forum Discussion

austin316's avatar
austin316
Regular Visitor
3 years ago
Solved

Creating two columns cash book

Hello Experts.

Am having a bit of some challenge, trying to implement a two-column cash book logic as an expression

on sql server report builder

 

The idea is to subtract [Debit amount] – [Credit Amount] = [Balance]

However, am unable to write an expression that subtracts OR add automatically to the [balance] as a result of input on the [Debit amount] OR [Credit Amount] columns  .

 

 

expression
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi  austin316 ,

    In my opinion ,it is earsier to get this in Power BI Desktop , you could add column in Power BI Desktop ,then publish to service ,and use Power BI Report Builder to get data from Power BI Service.See below for a quicker way to get the results you want, and don't bother with the complex statements in the Power BI Paginated Report Builder.

    fact = CALCULATE(SUM('Table'[Debt]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))-CALCULATE(SUM('Table'[credit]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

     

     

     

    Best Regards

    Lucien

     

4 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    Hi austin316 ,

    What I uesd is  Power BI Report Builder，you could test it to create new column:

    .Difference between Power BI Report Builder and Microsoft Report Builder：

     

    Power BI Report Builder is optimized for RDL reports in the Power BI service, which can log into Power BI, allow you to open and save reports from the workspace, and support querying Power BI advanced datasets and datasets of non-advanced users that have been published to the service.

     

    base data:

     

     

    new field:

     

    Output result:

     

    Best Regards

    Lucien

  • austin316's avatar
    austin316
    Regular Visitor
    3 years ago

    thanks for the above.

    However what I desire is like below. 

     

    DateDebtcreditBalance
    8/8/2022404
    8/9/2022040
    8/10/202210010
    8/11/202210020
    8/12/20220515

     

     1. [ 8/8/2022]  Debt - Credit = [Balance]

    2. [8/9/2022] what ever comes on either the [Debt or Credit] affect [add or substract ] the [balance]

     

    Note: Not calcualting individual lines seperatly.

    • v-luwang-msft's avatar
      v-luwang-msft
      Community Support
      3 years ago

      Hi  austin316 ,

      In my opinion ,it is earsier to get this in Power BI Desktop , you could add column in Power BI Desktop ,then publish to service ,and use Power BI Report Builder to get data from Power BI Service.See below for a quicker way to get the results you want, and don't bother with the complex statements in the Power BI Paginated Report Builder.

      fact = CALCULATE(SUM('Table'[Debt]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))-CALCULATE(SUM('Table'[credit]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

       

       

       

      Best Regards

      Lucien

       

  • dominek's avatar
    dominek
    New Member
    3 years ago

    It may be easier to solve this problem through Excel. Enter the formula in the line and get the report. I learned this in computer science college when we were taught these tables. Sometimes I missed school, but I still managed to study because of some services https://gradesfixer.com/free-essay-examples/freedom/ Then I worked at the cash register and dealt with similar tables