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,

 

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

    • 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