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,

 

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

    • sven7's avatar
      sven7
      Frequent Visitor
      8 years ago

      Hi MFelix,

       

      I used both ways, I am so far that it shows the balance or difference by each month, but not the rolling value. Also it is only showing 2017 and no previous years. 

       

      Any further tips are much appreciated thank you so much for your help.

       

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

        Hi sven7,

         

        My error mixup two different solutions I was working on.

         

        Create the relationship between both tables and then add this measure:

        Balance = 
 TOTALYTD(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),'Calendar'[Date])

        The other formula  without links was to make it month on month no matter the Year so the balence from december would go to January

        In that case you need to use the formula:

        Balance_1 = 
var dimdate = MAX('Calendar'[Date]) 
return
CALCULATE(SUM(Money_In_Out[Amount received])-SUM(Money_In_Out[Amount taken]),Money_In_Out[Date] <= dimdate)

        and don't connet the tables.

         

        Regards,

        MFelix