Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

 

Matrix Report.png

 

 

power query table.png

 

 

 

1 ACCEPTED SOLUTION

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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

12 REPLIES 12
AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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, 

 

example dashboard.png

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])

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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?

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

I clicked by mistake the "solved" button, sorry 😕

 

When I tried to create the new column, I had always an error on the EARLIER and more specific the red line was under the letter L, any clue?

Can you share a screenshot of the error? It doesn't usually provide error mid function but should hopefully be just a syntax mistake.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Sure, here is the error and the function that I used

 

(the error that I mentioned doesn't exist anymore, but a new one came to the surface)

 

screenshot_error.pngscreenshot_func.png

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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Great, problem solved! The file that you sent me helped a lot! Thanks!! 🙂

You're welcome!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

The high level idea/problem that I have is that in the screenshot,

 

While I load each month a new excel, automatically with the power query it goes under the previous month and in order to make the final report I have to first put the values in columns by month and then calculate the changes. (so I can easily create more columns such as year,6-month,quarters KPI's etc.)  

 

 

 

plan.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.