Me and Rob were discussing a chapter from our new book: Chapter 25 – Time Intelligence with Custom Calendars: Greatest Formula in the World (GFITW). (This chapter received a complete overhaul by the way. There are many new/updated chapters in the 2nd Ed book).
I was saying that the GFITW pattern had wide applicability, even outside custom calendars, and was arguing that we should encourage our readers to read that chapter even if their work did not involve a custom calendar (e.g. a 4-5-4 week calendar, see sample or learn more).
Rob stopped me and asked me to provide an example. Put on the spot, I drew a blank. But I didn’t have to wait too long to run into my old friend: the GFITW pattern.
In the spirit of the Beauty Pageant Power BI Training Exercise, here is another one. And hint, the solution involves the GFITW pattern.
For this post, we won’t keep you waiting, and present the problem and the solution together. We do encourage you to take a shot at the problem before you review the solution.
Note: We have simplified the scenario for the purposes of this training exercise.
Watch the video below or read on for an overview of the problem scenario.
PowerPivotPro ran a crowdfunding campaign, offering multiple perk levels, at which our supporters could contribute. These perks were cumulative as shown in the image below:
Thus if you ordered the Printed Book (#3), you would also receive DAX sticker (#2) and the e-book (#1). If you ordered the DAX T-shirt you would receive the poster, printed book, sticker and e-book.
Getting a count of all contributions by selected perk is easy. But what we really want is the measure below, which gives us a contribution count for “Selected OR HIGHER Perk”. This way we can determine, for example, how many e-books we truly need to deliver.
The DAX measure that we need to define
Please take a fair shot at solving the problem yourself before taking a peek at the solution.
Here is the formula using the GFITW pattern (notice the similarity with the image at the top of this post):
ContributionCount_Selected Level or HIGHER:=CALCULATE (
ALL ( Perk )
, Perk[Perk#] >= MIN ( Perk[Perk#] )
Greatest Formula in the World (GFITW)
Once I had set up the tables, my mind immediately went to the GFITW pattern.
The GFITW pattern is often demonstrated in the custom calendar scenario. The basic workings of the pattern rely on a Numeric ID which identifies the custom calendar period.
GFITW on Custom Calendars requires a numeric PeriodID column
Then GFITW operates by
a) Clearing all filters
b) Then using some navigation arithmetic to select the needed period IDs for the calculation
The specific navigation arithmetic used depends on the measure you are attempting to write – Year-Over-Year, Year-to-Date, Prior Period, Rolling period etc.
In our case, we need to employ a similar strategy not on a custom calendar table but on our Perk table.
If you would like to learn more about the inner workings and application off the GFITW pattern, you can grab a copy off our e-book today on MrExcel.com.