This topic contains 9 replies, has 3 voices, and was last updated by 
-
Posts
-
I have a dataset that I’d like to have development factors applied to the premium amounts based on if they are in the current month, one month ago two months ago, etc. It has to be flexible based on today’s month or the month of a specified date. The attached file has two tabs, the first is some sample rows of premiums and the second tab has the hypothetical development factors for the most recent 5 months.
Thanks in advance for any help anyone can provide.
Carl,
Please see attached workbook.
Used Power Query to create and shape tables before importing into Power Pivot. The only manual code required was to change the data type for the Group Code (Power Query interpreted the column as numeric, and I changed it to text).
Used a disconnected table to create a list of “specified” dates.
If a user selects more than one date on the slicer, Power Pivot will only acknowledge the earliest date selected. This is confirmed by a cube formula which shows the user the current specified month from Power Pivot’s point of view.
Disconnected tables are discussed in Chapters 12 and 13 in Rob and Avi’s book, “Power Pivot and Power BI”.
Tom
Tom,
I used to work with this guy so stop doing his work!!!! 🙂
Also, this may already be built out for him, he’ll just have to open up a lot of files on the network to find it!
My suggestion would be to have a disconnected table with group code, month offset, and factor going vertically not horizontally. Make a harvester measure to get factor and then use switch true to fetch the factor. I used the difference between NOW() and report date to filter on Month offset, but if you don’t like NOW(), you can take max report date vs report date. I don’t want to type it all out because I think it’s more fun to figure it out, plus the book is sitting right to the left of him! This can all be done in PowerPivot.
I also like the way you did it to Tom! Thanks for sharing.
Mike
Mike,
Always good to hear from you.
Appreciate the info,
Tom
HA Mike you turncoat! You’re right the book is sitting right next to my PC. Problem is I didn’t know what the topic would be to look up in the book. I’ll dig around the network for a bit to see if I can find the file (I know the modified date can’t be after a certain date LOL) if I can’t I’ll try both approaches. Thanks for your help.
Hey Tom, quick question. Do you use PowerBi a lot? If so, do you find that you can do everything you can do in Powerquery in excel, you can also do in PowerBi? Are you the moderator of the PowerBi forum?
Hopefuly Carl won’t be mad that I hijacked his thread. However, it may actually turn out to be relevant for him.
Thanks,
Mike
Mike,
I only do some of my work in Power BI.
When working inside the query editor of Power Query, I believe practically whatever you can do in Excel you can do in Power BI Desktop, although layout of the query ribbon is a little different.
I think a point to consider with Power Query and Power BI is that Power BI only implements “Power Query” in Power BI Desktop, not in PowerBI.com. However, since Power BI Desktop is used to create reports that can be published to PowerBI.com, the point is practically moot.
Almost all of my moderating time is spent on the Power Pivot side. Eventually I plan to do more in the Power BI forum.
Tom
Thanks Tom,
Do you ever have a user input data in an excel table to use as a parameter in PowerQuery? I’m trying to figure out the equivalent in PowerBi desktop. Not sure if the new parameter feature would work in PowerBI desktop because I would like the end user to pick the parameter, not the developer, if that makes sense.
Thanks,
Mike
Mike,
I have used an Excel table with parameter that are captured in Power Query using the technique in “M is for (Data) Monkey” by Ken Puls and Miguel Escobar (Chapter 23, “Dynamic Parameter Tables”).
I have also created Power Query queries that extract information from other queries, such as a date range, as an alternative to using a parameter table. This might be the closest equivalent in Power BI to using a parameter table in Excel.
—
On a side note (not even a tangent!) one of my areas of interest right now is using templates or patterns to quickly generate DAX measures and calculated columns for new customers, presentations and interactive discussions. Not 100% sold on the idea, so right now am exploring. One specific advantage I can see is that often more complex measures are driven by lower level measures and so a template based system could generate all of the upper level and lower level measures in one pass and push them into the clipboard for quick copy and paste. The “Italians” (Russo and Ferrari) have done a lot to officially establish patterns for DAX, but (collectively speaking through general purpose books, specific blogs and courses) so has Rob.So, anyways, excuse the digression. However, if you can see/think of any benefit, disadvantage or caveat to using software to generate the DAX for measures and supporting calculated columns, I would also be interested in hearing your thoughts. Should also say that I have worked with code templates on other production projects so I am not completely new to the idea or implementation.
Tom
TOM!
I was thinking the same thing! I also have a slightly different thing to add to the idea, but I think I’ll email you privately. Is your email public? If not, are you able to see my email from logging in to this site? If you can, can you shoot me an email.
Thanks,
Mike
The forum ‘Power Pivot’ is closed to new topics and replies.