Search found 9 matches

by CarbonChauvinist
Sun Sep 03, 2017 12:48 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 692

Re: Iterating through Subsets to Find Previous Value

Amazing. Works perfectly with the admittedly small sample set, will be able to test against the full data set on Monday and will be sure to confirm at that time.

Trying to unravel how it works piece by piece on my own, but will love to read your blog on it if you do decide to do so.

Cheers.
by CarbonChauvinist
Sat Sep 02, 2017 5:55 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 692

Re: Iterating through Subsets to Find Previous Value

MattAllington wrote:I'm willing to give this a go, but I need test data. Can you post a sample workbook with your wip measure


Thanks Matt!!

Here's a very, very basic sample using a PowerPivot linked table in the workbook - only has records for three members, but hopefully should work.
by CarbonChauvinist
Sat Sep 02, 2017 5:39 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 692

Re: Iterating through Subsets to Find Previous Value

Well I've figured out a way to get to my goal though I'm sure it's not the most efficient way and I had to create a helper Calculated Column to assist. Here's what I've ended up doing. I first added a helper Calculated Column named "Min Change ID for Member" that computes the minimum Chang...
by CarbonChauvinist
Sat Sep 02, 2017 1:44 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 692

Re: Iterating through Subsets to Find Previous Value

Testing this shows that I'm not quite there yet. For instance on the original example's member's records it appears to work as expected (see screen grab below for reference): seems_to_work.PNG As you can see, except for the first Status change (Changes[id] = 3100 and Changes[start_date] = 1/28/1998)...
by CarbonChauvinist
Fri Sep 01, 2017 7:27 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 692

Iterating through Subsets to Find Previous Value

Working with a member club to gain some insight into their membership usage patterns and have been tasked with putting together a report that studies how often members change their member Types and/or member Status. So member Status, simplified, is whether they are an active member or not; meanwhile...
by CarbonChauvinist
Fri Sep 01, 2017 3:45 am
Forum: PowerPivot/DAX
Topic: Modifying Query Context with ALL()?
Replies: 7
Views: 1190

Re: Modifying Query Context with ALL()?

Hi CarbonChauvinist, you can use this simple measure instead of the one you show. It has the advantage of not referring to hardwired years (2016, 2017 etc …) : Number of Sales Days in Year := CALCULATE ( [Sales days], VALUES ( 'dCalendar'[Year] ), ALL ( 'dCalendar' ) ) The measure will react to the...
by CarbonChauvinist
Wed Jun 21, 2017 5:46 am
Forum: PowerPivot/DAX
Topic: Modifying Query Context with ALL()?
Replies: 7
Views: 1190

Re: Modifying Query Context with ALL()?

Thanks for the help and time Matt, I do appreciate it. Based on your clues I was able to get the following to work for my needs: Sales Days:=CALCULATE(DISTINCTCOUNT('fPSGSales'[creation_date])) Number of Sales Days in Year := IF ( HASONEFILTER ( 'dCalendar'[ps...
by CarbonChauvinist
Sat Jun 17, 2017 3:15 am
Forum: PowerPivot/DAX
Topic: Modifying Query Context with ALL()?
Replies: 7
Views: 1190

Re: Modifying Query Context with ALL()?

Wow, thanks so much for the helpful reply Matt; very much appreciated! I'll definitely take to heart your best practice recommendations re: table naming conventions moving forward, thanks again. I also watched both videos in your link, and will be re-watching the one dealing with evaluation context ...
by CarbonChauvinist
Thu Jun 15, 2017 6:57 am
Forum: PowerPivot/DAX
Topic: Modifying Query Context with ALL()?
Replies: 7
Views: 1190

Modifying Query Context with ALL()?

I’ve been tasked with putting together a report that lists the top N sales days in a given time period which I’ve been able to do successfully. This is a simple report with only two tables, the fact sales table (fSales) and a calendar table (dCalendar) (see attached screen grab for reference). fsale...

Go to advanced search