Analyse repeat clients in different financial years

[Ashish Mathur]

Hi,

I am trying to analyse repeat clients data for different financial years. My question is very clearly explained in the Question worksheet of the file at the following link - http://sdrv.ms/10giNL6.

At the very outset, I must mention that Gerhard Brueckl has answered my question at this link - Analysis of repeat clients

However, I am trying to identify the error in my calculated field formula. This is purely for learning purposes.

I would really appreciate your help.

Thank you.

 

[MD610]

The HASONEVALUE() test should be wrapped around the COUNTX() portion of your measure since that is the part that is actually checking for Fiscal Year VALUES(). It was hard to follow where exactly all your paranthesis were grouping everything.

That will eliminate the error, however, even without the error, I do not believe the logic in the rest of your measure is providing you with what you need. In particular, the GENERATE() is returning a table of all Organised By with all Fiscal Years for each client regardless of whether or not they had activity in that Fiscal Year. Look at the SUMMARIZE() in my working measure. Because of the relationship between Feedback and Calendar1, I am able to SUMMARIZE fields from both tables which results in a table of Organised by and their grouped Fiscal Years with activity.

 

[Ashish Mathur]

Hi,

Thank you very much for helping me so far. Even if the GENERATE function generates all possible rows, the formula should work because no activity will simply count as 0. So this should not be the problematic factor.

I reckon that the error is with the FILTER() portion of my formula where I am trying to filter dates between 2 years prior and the existing year. I think if I correct for that portion, I will be able to solve the problem.

As a test, I tried the following:

1. In the column labels, I dragged the fiscal years from 2007 to 2013. So 2012 in the column label would mean 1 April 2012 to 31 March 2013 and so on for the others
2. I then wrote a calculated Field formula [2 years prior] to compute the date two years prior to each fiscal year shown in the column labels

=edate(ENDOFYEAR(Calendar1[Date],"3-31"),-24)

So for FY 2012, this measure shows the date as 31/3/2011, for FY 2011, this measure shown the date as 31/3/2010

3. I then wrote a calculated Field formula [Current year] to compute the last date of each fiscal year shown in the column labels

=edate(ENDOFYEAR(Calendar1[Date],"3-31"),0)

So for FY 2012, this measure shows the date as 31/3/2013, for FY 2011, this measure shown the date as 31/3/2012

4. Now to compute the total number of days between the two financial years, I wrote the following calculated field formula

=COUNTROWS(DATESBETWEEN(Calendar1[Date],[2 years prior],[Current year]))

This formula reveals the correct result. For FY 2012, the number of days are 732 i.e. days between 31/3/2011 and 31/3/2013. The result appears correct for other fiscal years as well. So no problem so far.

5. I now tried this variant but my result do not match with what I get in step 4 above

=COUNTROWS(FILTER(Calendar1,Calendar1[Fiscal Year] <= year([Current year])&&Calendar1[Fiscal Year] >= year([2 years prior])))

The result of this calculated field formula is the 365 for FY 2012 and 366 for FY 2011.

Why is this happening. Should the formula in step 5 reveal the same result as the formula in step 4 above. Where is my mistake?

Thank you for your time.

 

[MD610]

I'm not by a computer at the moment to test, but try wrapping the Calendar1 in the first argument of your FILTER() in step 5 with an ALL().

FILTER() is evaluating in the context of the pivot on the table you specify in the first argument. It only sees one fiscal year because of the pivot. FILTER() subtracts from the filter context. It doesn't add to it unless you "force" it to do so.

Adding ALL(Calendar1) should allow FILTER() to "look" at your entire calendar table and then apply the filter context.

Let me know how this works out for you. If not, I will investigate further tomorrow.

 

[Ashish Mathur]

Hi,

thank you for your prompt reply. That returns an incorrect result of 5144 for all financial years. I modified the formula to this

=COUNTROWS(FILTER(all(Calendar1),Calendar1[Fiscal Year] <= year([Current year])&&Calendar1[Fiscal Year] >= year([2 years prior])))

Should I upload the file for you to look at?

 

[MD610]

Please upload it. I would be happy to give it a review in the morning.

 

[Ashish Mathur]

Hi,

File is at this link - http://sdrv.ms/150nkC2.

Name of file is Ashish Example IV

 

[MD610]

I think your issue with the step 5 formula is that your base measures of Current Year and 2 Years Prior don't work correctly within the context of the FILTER() without having it all contained within a CALCULATE() that provides additional filter context. FILTER() is only operating within the context of what has already been filtered by the pivot rows/columns. Adding the ALL() removes that contraint but then I believe it also impacts your base measures in a way that causes issues.

For more detail on how FILTER() works and how it can be applied to custom time calculations please see the posts related to The Greatest Formula In The World on PowerPivotPro:
The Greatest Formula in the World, Part 3 « PowerPivotPro

If I eliminate your base measures and do the time logic directly in the FILTER(), it looks like it works fine:

=IF(HASONEVALUE(Calendar1[Fiscal Year]),
              COUNTROWS(
                                FILTER(
                                      ALL(Calendar1),
                                           Calendar1[Fiscal Year] > VALUES(Calendar1[Fiscal Year])-2 && 
                                           Calendar1[Fiscal Year] <= VALUES(Calendar1[Fiscal Year])
                                 )
                ),
                BLANK()
)