98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Custom column range DAX
Custom column range DAX
Resolved · High Priority · Version 365
Thu 3 Nov 2022, 09:21
Edited on Mon 7 Nov 2022, 07:42Delegate Martin said...
Martin has attended:
Power BI Modelling, Visualisation and Publishing course
Custom column range DAX
Hi,
I'm trying to create a custom column which SUMS a range of other columns together but not by column name as the name is variable, just position.
i.e. Not Jan+Feb+Mar
Rather Column 1+2+3
That way if the data changes/moves then it still adds up column 1+2+3
I do not have a column with dates as values just month headed columns with numbers as values in.
Thanks in advance, can't seem to find right answer on google or forums.
Sun 6 Nov 2022, 11:31Trainer Jens said...
RE: Custom column range DAX
Hi Martin,
Thank you for the forum question.
Sorry but your question sounds like you may not have your data model structured the right way. The tables in the data model must be normalised tables so all new data adding more rows to the table not columns, and the column names must not change.
Everything in Power BI is based on names. So the names must stay the same otherwise you can get a lot of issues. The query may be able to handle name changes, so you in the connection to the data change the names. If the query change the names in column 1, 2, and 3 to may be Calc1, Calc2, and Calc3, then you will always have the same headings in the columns you want to calculate.
I do not know your data set, and I do not know, if you can use this solution.
I would unpivot the data in the query to get the structure right. Sorry again I do not know your data model and data set, but the data must be in list format and grow down not across.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 7 Nov 2022, 07:43Delegate Martin said...
RE: Custom column range DAX
Hi Jens, thanks for your response.
The data is fed with month column headers containing values for number of orders, i.e. Jan-2022 and 15
The custom column value would equal 'last 3 months' orders.
As time moves on the fed data changes for the position of Jan becomes Feb becomes March and so on. So the column name changes but the position does not and the custom column needs to adapt.
There are other descriptive columns in the data besides the months. What do I need to pivot to get me in the right direction and does it matter when my column headers suddenly change name or get added to?
Mon 7 Nov 2022, 09:55Trainer Jens said...
RE: Custom column range DAX
Hi Martin,
You will have to unpivot the table.
Please see the video below from Youtube
https://www.youtube.com/watch?v=ESap6ptV8fI
Where do you get the source data from?
I guess from Excel or CSV file.
If the video doesn't help you, I will need you to send me some dummy data in an Excel file. I need one table which are structured, how you have the data one month, and another table how the data are the next month.
If you need it, please send the dummy data to:
info@stl-training.co.uk
Please write that the file is for me.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 7 Nov 2022, 17:25Trainer Jens said...
RE: Custom column range DAX
Hi Martin,
Your data is structured wrong and you will need to unpivot the data.
Please watch video below. This will tell you how to unpivot your data.
https://www.youtube.com/watch?v=W6UeZCte9YM& ;t=89s
The measure below will then calculate last 3 months
Sales last 3 months =
CALCULATE (
SUM ( Table[sales] ),
DATEADD ( 'Date'[Calendar Date], -3, MONTH )
)
I assume you have a date table in the model. The Dateadd function will need the primary key from the date table ('Date'[Calendar Date])
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 8 Nov 2022, 10:14Delegate Martin said...
RE: Custom column range DAX
Hi Jens,
Much appreciated, starting to get there now. Will email a screengrab. Added a calc for month shift and another with full DAX which Power BI is ok with but adding the column produces no value?
Tue 8 Nov 2022, 18:35Trainer Jens said...
RE: Custom column range DAX
Hi Martin,
In the Excel file you sent me, you had dates. In the screenshot you sent me you have month names.
You can only use time intelligence functions if you have dates, and the dates in the transaction table must be related to the dates table you also must have in the data model.
The DAX I sent you
Sales last 3 months =
CALCULATE (
SUM ( Table[sales] ),
DATEADD ( 'Date'[Calendar Date], -3, MONTH )
)
is a measure not a column calculation. The 'Date'[Calendar Date] argument is, if you have a date table (Date is the name of the table and [Calendar Date] the column, which store unique dates, which are used as primary key to connect to a foreign key in the fact table (the table with all the transactions)).
DATEADD is a time intelligence function and cannot be used without a date table.
DATEADD ( 'Date'[Calendar Date], -3, MONTH )
The data is now structured as a list, good. You can add the Date column to a matrix and Sum of usage.
I hope this make sense
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 9 Nov 2022, 11:06Delegate Martin said...
RE: Custom column range DAX
Much appreciated Jens! Still have much to learn. Will give it a go. All the best.
Tue 13 Dec 2022, 10:20Delegate Piotr said...
RE: Custom column range DAX
Hi Jens,
Thank you for all instructions provided.
I am investigating this problem all together with Martin, and I was wondering if you could help to navigate me from here.
I have read carefully and tried to adjust, DAX formula that you have send over:
Sales last 3 months =
CALCULATE (
SUM ( Table[sales] ),
DATEADD ( 'Date'[Calendar Date], -3, MONTH )
)
It is pretty much straight forward.
But you have also mentioned that to make this formula works, we will need a "primary key" element, which is "DateTable".
I created my "DateTable" which is used by simple formula "DatesTable = CALENDARAUTO()"
It generates all data using calendar dates from a current year.
Based on all data above, our target is to create a matrix chart which will present columns with (starting from left-hand side): Products and Months.
In rows we want to present sum of values for all products presented in the same chart.
We want to see a breakdown for last couple of months, additionally an extra column which will calculate sum for all products from the LAST 3 MONTHS, and it will "roll-over" using our "Sales last 3 months" formula, when new month starts.
The problem is that I have my matrix with all formulas I need, products, months, data model correctly adjusted, but still when I try to add extra column with my formula it doesn't format my matrix correctly and "mix-up" all data within it.
Any chance that you could help me with that?
If you need more details (visuals), I can send them over via email, but I would only need your address.
Kind Regards,
Piotr Jedrzejczyk
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Data Model ShortcutsCtrl + Shift + L: Create a relationship between tables. |
