Search found 992 matches

by MattAllington
Fri Oct 19, 2018 6:56 am
Forum: PowerPivot/DAX
Topic: How to calculate Variation between periods
Replies: 3
Views: 66

Re: How to calculate Variation between periods

The trouble with your period column is it is actually a text column with a human readable description of the period. On its own, this is not enough to do the job
by MattAllington
Thu Oct 18, 2018 6:02 am
Forum: PowerPivot/DAX
Topic: How to calculate Variation between periods
Replies: 3
Views: 66

Re: How to calculate Variation between periods

Welcome to a brave new world. There is a lot to learn, but you can learn it and you will be better for it. First unpivot your data so you have a single column of numbers and a “type” column. https://youtu.be/v8ccS5N6xFk Then you need a calendar table. https://exceleratorbi.com.au/power-pivot-calenda...
by MattAllington
Wed Oct 17, 2018 3:34 pm
Forum: PowerPivot/DAX
Topic: Data conversion + sum QTD/YTD
Replies: 1
Views: 86

Re: Data conversion + sum QTD/YTD

There is a lot you need to do here.

You need to unpivot your data using power query before you load. See here how to unpivot in Excel.

Then you need the custom time intelligence pattern. You can read about custom time intelligence in DAX in my blog.
by MattAllington
Mon Oct 15, 2018 8:53 am
Forum: Power Query
Topic: Power query - query first of prioritized group
Replies: 1
Views: 103

Re: Power query - query first of prioritized group

I don't understand what you are asking. Yes, it is possible to sort on one or more columns and then select the first item that appears in the table.
by MattAllington
Tue Oct 09, 2018 6:42 am
Forum: PowerPivot/DAX
Topic: Issue with Learn to write DAX
Replies: 1
Views: 110

Re: Issue with Learn to write DAX

This is a known bug with some versions of Excel. It should work, but it doesn't. What version of Excel are you using?

Unfortunately you will have to skip this process or reload the table if you want to make changes.
by MattAllington
Wed Oct 03, 2018 5:29 am
Forum: Power Query
Topic: New rows in table not formatted as Date
Replies: 2
Views: 156

Re: New rows in table not formatted as Date

I have seen this too - very frustrating. I haven't been able to solve it with the few files I have, but now I hear that you have a problem with some files and not other, I wonder if you remove the table (set data load to connection only) and then add it back in a clean sheet, how that would work out...
by MattAllington
Tue Oct 02, 2018 6:56 am
Forum: Power Query
Topic: Power query - join kind not showing
Replies: 1
Views: 91

Re: Power query - join kind not showing

I’ve never seen this before. I am wondering if you have any duplicates in either of the columns to be joined. Just a hunch, but maybe if there are no duplicates, then only an inner join works. Apart from that, I have no idea.
by MattAllington
Sun Sep 23, 2018 7:24 am
Forum: PowerPivot/DAX
Topic: Help with DATEDIFF function
Replies: 3
Views: 219

Re: Help with DATEDIFF function

Yes it makes sense, although I don't get why "contracts" are called "Budgets" :-). What format do you want the output in? Do you want a list of the contracts in a pivot table with the number of overlapping days displayed based on the period selected? What should the total do? Add...
by MattAllington
Sun Sep 23, 2018 7:10 am
Forum: Power Query
Topic: Power Query
Replies: 1
Views: 143

Re: Power Query

On the home tab there is a button "Replace Values". Simply replace the underscore character with a space character
by MattAllington
Fri Sep 21, 2018 7:57 am
Forum: PowerPivot/DAX
Topic: Combine 2 pivot tables into one pivot table
Replies: 1
Views: 93

Re: Combine 2 pivot tables into one pivot table

You can't combined 2 pivot tables. A pivot table is a visualisation tool for data. You need to combine the underlying data. You can do that in Power Pivot.

Go to advanced search