Hello all,
I’ve read through many of the posts and watched most of the videos regarding Customer Churn and believe that I have a fairly strong understanding of the concept of this formula, where a GU is a Giving Unit (donor):
Lost GUs =
VAR GUsGave = CALCULATETABLE(
VALUES( Giving[HeadOfHouse ID] ),
FILTER(
ALL( ‘Date’ ),
‘Date’[Date] > MIN( ‘Date’[Date] ) - 365 &&
‘Date’[Date] < MIN( ‘Date’[Date] ) - 60
)
)
VAR PriorGUs = CALCULATETABLE(
VALUES( Giving[HeadOfHouse ID] ),
FILTER(
ALL( ‘Date’ ),
‘Date’[Date] > MIN( ‘Date’[Date] ) - 60 &&
‘Date’[Date] < MIN( ‘Date’[Date] )
)
)
RETURN
COUNTROWS( EXCEPT( GUsGave, PriorGUs )) * -1
My request is to help me understand the use of MIN(), this so that I can adjust the differences in thought that I’ve grown into versus how that function is to be interpreted in this and other like scenarios.
The way I would read the statement beginning at the FILTER is:
* Consider ALL dates regardless of any existing filters
* Grab the [Date] from the current iteration of Giving[HeadOfHouse]
* When ‘Date’[Date] is greater than the minimum ‘Date’[Date] in the current time context - 365 days AND
* When ‘Date’[Date] is less then the minimum ‘Date’[Date] in the current time context - 60 days
I know this thinking needs to be adjusted, I simply can’t get over the hump to get there. Would one of you that have bridged the gap help me to get to the other side, as well?
MIN() in this case is basically just performing the same function as SELECTEDVALUE() in harvesting a scalar.
I agree that these types of measures can make it difficult to understand what’s going on. See if this helps - I rewrote the measure using a variable to pinpoint the current row value for Date[Date]. IMO that much more clearly illustrates what’s going. Also, formatting the measure really helps - to me, unformatted DAX still just looks like gibberish.
Finally, using a tool like DAX Studio or Tabular Editor 3 to visualize your DAX queries and materialize your virtual tables provides massive insights into what’s going on with a complex measure like this one.
Check out the video I did recently on the topic - I think it will really help you here.
I hope these “click the lightbulb on” for you. If not, perhaps others have a different way of thinking about this question that may resonsate with you.
@BrianJ Thanks for the help!!! And the rewrite…it does make it easier to understand looking at the formula this way. I’ll be viewing the “Debugging Virtual Tables” video shortly.
Out of curiosity, what would be the difference between MIN( ‘Date’[Date]) and MAX('Date[Date])? Will MAX() pull from the highest date value in the ‘Date’ table? Based on the understanding that MIN() is harvesting a scalar, I would assume that MAX() would do the same, but the results are vastly different.
Lastly, how do you get the code box to display in a post, as you’ve done? Normally, I would format using the </> icon in the toolbar, but that only indents the highlighted text.
Can you please post your PBIX work in progress? That will help me play around and provide a clearer explanation to your question.
In terms of the formatting, I would typically use </> as well, so that readers could copy and paste the code into their files, Analyst Hub, etc. In this case though, I just took a screenshot of the formatted results from DAX Studio, since I thought maintaining the colors might also help with your understanding.
Using MAX instead of MIN seems to be more representative of the values needed. In SQL I’ve created a script that pulls the Giving Units that have given within the past 12 months EXCEPT those that have given within the past 2 months. The results are slightly different for known reasons, but close enough to indicate that MAX provides the more accurate response.
It’s great to know that you are making progress with your query @ronkochanowski.
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @ronkochanowski, we’ve noticed that no response has been received from you since July 31. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
@BrianJ , good morning…wondering if you’d had a chance to review the .pbix file? Regarding the difference between MIN() and MAX() in this context, could it be stated that MIN() refers to the beginning of the period in question and MAX() refers to the end of the period? For instance, if the time context is Month, the MIN() would refer to the first of the month and MAX() would refer to the last day of the month.
Sorry, I misinterpreted your earlier post as indicating that you’d answered your remaining question through the SQL script comparison.
Glad to take another look, but do you have an in-progress PBIX file incorporating the measure we developed above, used in the context in which you want to employ it (i.e., mockup of the results you want to see)?
@BrianJ ,
The attached .pbix file has the measure from above incorporated into it, with the one adjustment…MIN() was changed to MAX(), for the reasons giving previously:
The original question, reposted earlier today, was to gain understanding or the difference in MIN() and MAX()… Do these function, within a date context, reflect the minimum and maximum of the period? in other words, if I have the date context set to a month timeframe, does MIN() reflect the beginning of the month and MAX() reflect the end of the month?
100% right. When I said MIN or MAX functioned as SELECTEDVALUE(), I was envisioning the context being daily, where min and max would be equal. But you are completely correct that when you shift the context to monthly, these functions operate exactly as you anticipate (see table below where for the Var Value measures I just returned the value of the CurrentDate variable in each measure).
In this case, you will need to make a determination as you’ve done as to which function min or max is more appropriate to the analysis you want to do.
I hope this is useful confirmation, but you now seem to have a very clear understanding of how this DAX code is working.
