Forum Discussion

sven7's avatar
sven7
Frequent Visitor
8 years ago
Solved

In and out-flow from money. Create difference and aggregate value by month.

Dear Power Bi Users,

 

I am struggling with a little issue right now. I did some research in this forum but did not find the right solution. Maybe I am just on the wrong path, this is why I hope there might be a simple answer. 

 

This is some sample data. I have a register of various accounts and money which went in and out on these accounts on a certain date. The date is transformed into a month format.

 

AccountDateMonthAmount takenAmount received
ZZZ Bank21.02.20172017-0210 
AAA Bank20.02.20172017-02 5
ZZZ Bank01.02.20172017-027 
ZZZ Bank01.03.20172017-03 21

 

Now I would like to create a table/formula which can reproduce the information to a total balance each month. So how much money we have in total, thus adding previous month etc. 

 

 ZZZ BankAAA BankCash
2017-01   
2017-02-175 
2017-0321  
2017-04   
2017-05   
2017-06   
2017-07   
2017-08   
2017-09   
2017-10   
2017-11   
2017-12   

 

With the data I want to create a stacked chart (accounts and their values by month), which should be pretty easy with having the information.

 

I thought about a CALCUATE Formula, but somehow I do not get it running.

 

I appreciate any advice or tips. 

 

Thanks in advance.

 

Sven

  • MFelix's avatar
    MFelix
    8 years ago

    Hi sven7,

     

    You are using a column you should use a measure, columns are always calculated at row level, measures are calculated at context level so taking into account the different levels of information.

     

    Change your column to a measure and it will work,

     

    Regards,

    MFelix

22 Replies

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

    Hi sven7,

     

    I would add a calendar table to you model but using your columns just add the following measure:

     

    Balance = SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken])

    then add it to your table.

     

    Regards,

    MFelix

    • sven7's avatar
      sven7
      Frequent Visitor
      8 years ago
      MFelix thanks for your advice. I was also trying this approach but it is only a partly solution. It only gives me the difference per month, meaning if I generated a plus or minus in the month. I also would like to know what is my current cash balance. So there would need to be like aome kind of aggregate from the previous month to see the total available money by month. Do you maybe also have a good idea to this point?

      Thanks in advance and Br

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

        Hi sven7,

         

        Add a calendar table and don't make any relationship between both tables.

         

        Then add this measure:

         

        Balance = 
var DimDate = MAX('Calendar'[Date])
return
 TOTALYTD(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),Money_In_Out[Date]<= DimDate)

        Regards,

        MFelix