Working with two dates - show amounts between two selected dates

Erik · March 6, 2018, 10:15pm

Hi All,

I’ve created a dashboard with visuals displaying data on new customers and lost customers. My data is coming primarily from a customer table that has an ‘open date’ and ‘closed date’ that represents the start and end of the customer relationship.

I would like to be able to select the date slicer and have all visuals for new and lost customers filter appropriately. Or, for example, select a day of month from a visual and have other visuals for new and lost customers filter appropriately for that day (i.e. new/lost by day, new/lost by branch, etc.).

Is this possible? I’m currently not using a date table b/c I can’t decide how to use it with 2 separate date fields. If this is not possible, is there some other way I should be thinking about this?

Thanks for any thoughts?

sam.mckay · March 6, 2018, 10:38pm

Some images would be helpful here showcasing the current formulas and visuals.

This is likely quite complex so need more context around what you’re looking at currently and what this needs to look like ultimately

Erik · March 7, 2018, 9:42pm

Thanks Sam-- I’ll post some images below with related measures (I may need to edit this as I’m not sure how it will display)…

Below is part of my dashboard which shows some visuals on new and lost members. I also have a 2 bar graphs for new and lost by Branch (not shown for size).

Measures:

Closed MBRS MTD = CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
                            ALL(ACCOUNT[OPENDATE]),
                            ACCOUNT[CLOSEDATE]>=DATE(2018,2,1),
                            ACCOUNT[CLOSEDATE]<=DATE(2018,2,28)
                            )


New MBRS MTD = CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
                                       ACCOUNT[CLOSEDATE]=0)

As mentioned, I’d prefer to have New and Lost member visuals respond to one date selection to make it more clear for the end user (rather than needing to select one for new and one for closed).

If this is not possible or feasible that’s fine I just thought I might be missing something basic.

Thanks!

sam.mckay · March 8, 2018, 1:19am

Ok I think I understand a bit better.

What is required here is a derivative of this technique showcased in the ‘Solving Analytical Scenario’ course at Enterprise DNA Online.

It’s called the ‘events in progress’ pattern

You definitely want a date table, but you need to have two ‘inactive relationships’ down to the fact table.

Run through this example, and set up your model this way, then let’s work on the formula if you can’t work it out from here

Erik · March 9, 2018, 10:01pm

Wow, I gotta say that it is amazing how this works (mind blown as this becomes more clear). I’m not sure I total understand all that is going on but it is cool. My setup was a bit simpler than your video example so I was able to do it. I did have a slight difference in the numbers for Closed Members (7) and so a few questions. What’s a bit confusing to me is how this inactive code works with Page and Vis. Level Filters.

I set up the date table and then made inactive joins to Account OpenDate & Account CloseDate.

New MBRS MTD = CALCULATE(DISTINCTCOUNT(
                                  ACCOUNT[ACCOUNTNUMBER]),
                                  USERELATIONSHIP('Date Table'[Date], ACCOUNT[OPENDATE]),
                                  ACCOUNT[CLOSEDATE]=0)

First regarding new members, note the last line in the measure above. I have to omit closed members (open & closed in same month). I didn’t think this world work because the closed date is inactive. However, the result is correct. I don’t totally understand, but I’m guessing by using the Acct Closed Date rather than the DateTable date, it works???

Closed MBRS MTD = CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
                            USERELATIONSHIP('Date Table'[Date], ACCOUNT[CLOSEDATE]),
                            ACCOUNT[OPENDATE]<DATE(2018,3,1)  )

Regarding Closed Members above, the last line is from the original measure and is to make sure all closed members are existing Members (not opened and closed during the current month–March). It would be nice not to ‘hard code’ the date but I’m not sure if that is possible. Again, this seems to be working even though I used the ‘Acct Opendate’ (inactive) rather than the DateTable date.

Thanks for your help on this. I definitely learned something on this one!!

sam.mckay · March 10, 2018, 8:51pm

This is really interesting. There’s a lot going on, so I’ll have to test each aspect of it just to make sure.

With the hardcoded date, you should be able to make this dynamic somehow. What dynamic date do you want this to be? Today or some other date?

Erik · March 11, 2018, 12:14pm

Thanks, yes. This has turned out to be quite complex from what I first thought was a simple dashboard (but very interesting).

Regarding the date, it should always be the first day of the month I’m analyzing.

So, in this case, the ‘Closed Member measure’ is looking for closed members in March (and I have a date Slicer in the report that sets that date range). Then I need to make sure the open date was prior to March (to exclude members open and closed in March–this is the date I have hard coded now).

Basically, I’m looking for those closed in March but restricting it to those opened prior to march (those opened and closed in March are likely errors and shouldn’t be counted). So, less than 1st day of month. Hope that makes sense.

sam.mckay · March 11, 2018, 11:20pm

Yes just reviewed this.

On the first formula, you’re right. The DISTINCT count is evaluating over the accounts table, and so is the last line for closedate. So the relationships aren’t really relevant and that’s why you’re getting the correct answer.

Same again on the bottom formula

sam.mckay · March 11, 2018, 11:22pm

To dynamically get the first date of every month. Use this formula within the measure

First Date Of Month = 
CALCULATE( MIN( Dates[Date] ), ALL( Dates ), VALUES( Dates[MonthName] ) )

Erik · March 12, 2018, 2:15pm

Nice, thanks. I had to take out the “All(Dates)” statement of that formula to get it to work. It was giving me the correct month and day but it was giving me the first year in my date table (which is well in the past).

However, I’m having trouble using the ‘First Date’ measure in the DAX statement…

Closed MBRS MTD = CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
                            USERELATIONSHIP('Date Table'[Date],ACCOUNT[CLOSEDATE]),
                            ALL(ACCOUNT[OPENDATE]),                            
                            _ACCOUNT[OPENDATE]<[First Date of Month])_

No matter how I format the last line or what formula I try, I’m getting some sort of syntax error. How must I format that date comparison in the last line to get this to work with the measure? (I tried several variations, the above was just the latest).

I’m wondering, given the “First Date” Measure you created above, how do I know if “First Date” is using the open date or closed date? The way I think I understand it, it would be based on the "userelationship’ set in the measure where “First Date” is being used. In the above formula, that would be closed Date. So, if that is true, in the last line, I’m comparing open date to closed date (which might explain the syntax error --“true/false not allowed in Calculate function”).

Then again, I could be totally wrong. I’m a little confused about this.

sam.mckay · March 12, 2018, 8:12pm

I’m sorry slight error my end, because yes that will also go to the first year. Slight change required.

First Date of Month = 
CALCULATE( MIN( Dates[Date] ), ALL( Dates ), VALUES( Dates[Month & Year]) )

You see the VALUES part needs to be the month & year column versus just the month

Also the other problem your having should be fixed by placing this formula into a variable.

See below for the syntax

Closed MBRS MTD = 
VAR FirstMonthDate = CALCULATE( MIN( Dates[Date] ), ALL( Dates ), VALUES( Dates[Month & Year]) )

RETURN
CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
    USERELATIONSHIP(‘Date Table’[Date],ACCOUNT[CLOSEDATE]), ALL(ACCOUNT[OPENDATE]),
        ACCOUNT[OPENDATE]<FirstMonthDate)

The first date is just calculating the first day of the month, no relation to open or closed dates. But with the logic you have in your formula you are then evaluating if the open date is less than the first date of any month in the current context.

To me it’s looks ok now, see how you go with this.

Erik · March 12, 2018, 9:05pm

Yes, that did it!!

This level of DAX (with VAR, etc) is over my head at this point but I understand the concept and that will give me something more to study. One final question. I’m curious why a VAR was required and the Measure didn’t work?

Thanks so much for your detailed help with this!! This is going to be useful in a lot of my visuals!

Erik

sam.mckay · March 12, 2018, 9:11pm

It’s due to the context for the calculation within the particular measure.

When you evaluate the first date outside the CALCULATE statement in the variable, you evaluate this first. (So it’s similar to if you just calculate this as one measure)

When the first date is calculating within CALCULATE then it is working through logic at a different context. In this case row context.

This takes a while to get your head around, but once it settles in, it will make complete sense.

Reviewing the Mastering DAX Course content around ‘context’ will help