Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subtract columns

Hello,   My end goal is to make an automated reporting procedure, where I will copy/paste every month a new excel file (trial balance of each month) on a folder and then the outcome will appear on ...
Need Help
Show and Tell
Tutorial Requests
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    It looks like you are trying to create a calculated measure. You need to do this as a COLUMN. 

     

    See attached pbix file below signature for reference on what I have done. 

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago
Is your actual data missing rows for some months? I'm assuming you want to filter for the [account number] = current row account number, but for the subtraction, are we looking for Feb always if current row month is March, or are we simply looking for the previous entry for that same account number, even if the latest one before March is in Jan instead of Feb? Hope that makes sense...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hey Alison,

 

Thanks for the reply. In my actual data there are thousands of rows (account number) each day, I skipped some months in my dummy example. Each account number shows the cumulative ammount, so every month gets bigger and bigger BUT there are also some months that they remain the same (i.e. they don't move). 

 

Let me give you another example, 

 

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    Try something like this:

    Change = CumulativeTotals[Value] - MAXX(FILTER(ALL(CumulativeTotals),EARLIER(CumulativeTotals[Date])>CumulativeTotals[Date] && CumulativeTotals[Account Number] = EARLIER(CumulativeTotals[Account Number])),CumulativeTotals[Value])
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I got this type of error,

       

      "A single value for column 'CumulativeTotal' in table 'Merge' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

       

      Any ideas or suggestions?

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago
        You need to do this as a calculated COLUMN in the same Merge table. The formula I suggested will not work as a measure because of the way the EARLIER function is used.