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 th...
  • 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

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

sven7's avatar
sven7
Frequent Visitor
8 years ago

MFelix Thanks again for your help. 

 

I have tried this approach, but it only gives me the same value for all dates. I have created a seperated table with the calendarauto function, made sure there is no connection to other tables and put in your formula and adjusted it according to my names. 

 

Do you have an idea why there is the same value everywhere?

 

Thanks in advance and BR

Sven

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi sven7,

     

    How did you create the visuals? It seems this simple measure is enough. You can see it from the snapshot.

     

    Measure =
SUM ( Table1[Amount received] ) - SUM ( Table1[Amount taken] )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • sven7's avatar
      sven7
      Frequent Visitor
      8 years ago

      Hi v-jiascu-msft

       

      thanks for your tips. I believe that my description was not accurate enough. I would like to see how the available cash balance increases or decreases by month and see the total current cash balance in the latest month. I have already created the graph in Excel, but I would like to create the formula in Power BI because the Excel dta is outdated.

       

      Our different accounts shall be reflected in a stacked chart by the balance each month and aggregate until the latest month. I only imported the data sheet from Excel. 

       

       

      Thanks again for all your help in this community.

       

      BR Sven

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

    Hi sven7,

     

    What is the month column you have in your table is it from calendar or from you data table?

     

    Regards,

    MFelix

    • sven7's avatar
      sven7
      Frequent Visitor
      8 years ago

      Hi MFelix,

       

      the month column in my table draws the month from the date a booking has been made on an account. 

       

      Month = Format(Logs[Date];"yyyy-MM")

      This is how my calendar table looks like, there is no connection to the other sheet.

       

      calendar = CALENDARAUTO(12)

      BR Sven

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

        hi sven7,

         

        Create a month column on your calendar table and place that column on your visual

         

        Regards,

        Mfelix