Forum Discussion
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 a Power BI dashboard in a matrix format where each column is a month and other columns are Q1,Q2,6-month, etc. Each row represents balance sheet's account numbers. The original files have >100k rows/accounts and the date is in a format D/M/YYYY.
My problem is that the trial balance has cummulative numbers, so I have to substract each column to have the outcome for a specific month i.e. Mar - Feb = Change in March. Any idea on how can I tackle this problem?
Unfortunatelly, I cannot share my original data so I created the power query table and the reporting matrix on the dashboard with dummy numbers.
Thanks in advance!
Cheers,
Alex
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.
12 Replies
- 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...
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,
- 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])