Count of "yes" based on latest date within time frame

[ThaddeusB]

I have data that looks something like this:

ClientID	Date	Success
1	1/2/2018	Yes
1	1/21/2018	No
1	2/5/2018	Yes
2	2/21/2018	No
2	3/3/2018	Yes
3	2/15/2018	Yes
4	1/4/2018	Yes
4	1/8/2018	No
4	1/20/2018	Yes

I have a date slicer based off a "calendar" table.  What I am looking for is a count of "yes" values based on a client's last value within the selected date period (displayed in a card or whatever).  So, for example, selecting a range of 1/1-1/31 would produce a count of 1 (client 4), while a range of 1/1-2/28 would produce 3 (#1,3,4).  

Thanks in advance for your help.

[OwenAuger]

@ThaddeusB

You could use a measure like this.

It creates a filter corresponding to each Client paired with the latest date for that client (subject to the selection on the Calendar table), intersected with Success = "Yes", then counts the rows of the table (I've called it ClientData).

Latest Yes Count = 
CALCULATE ( 
    COUNTROWS ( ClientData ),
    GENERATE (
        VALUES ( ClientData[ClientID] ),
        LASTDATE ( ClientData[Date] )
    ),
    ClientData[Success] = "Yes"
)

Does this work within your model?

Regards,

Owen

[ThaddeusB]

@OwenAuger

Thanks for your help.  My slicer is based on a generated calendar (to show all dates, not just those that have a value), so I think the calculation needs to directly reference the calendar table.  (I could be mistaken).  For example, my count of clients within the date range formula:

Clients Served in range =
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN
   CALCULATE (
      DISTINCTCOUNT(Data[Client Id]),
      FILTER (
         Data,
         ( Data[Eval Date] >= minDate && Data[Eval Date] <= maxDate )
      )
   )

Also, there are a few duplicate dates in the data set, so LASTDATE gave me an error.  I can add a few seconds to a date at random if needed to avoid this, but do you have another suggestion?

[OwenAuger]

@ThaddeusB

Thanks for testing that out!

I'm assuming the column I had been referring to as ClientData[Date] has a relationship with 'Calendar'[Date], so the slicer on the 'Calendar' table is filtering ClientData -  is that correct?

I'm just asking because your Clients Served in range measure appeared to be written as though a relationship didn't exist and is simulating a relationship using DAX.

If the relationship exists, there's nothing inherently wrong with referencing the fact table's Date column within the measure, since we are wanting to narrow down the 'Calendar'[Date] filter further and it's a bit more convenient to do that using the fact table Date column.

Given the issue with LASTDATE and duplicate dates, we can instead use LASTNONBLANK:

Latest Yes Count = 
CALCULATE ( 
    DISTINCTCOUNT ( ClientData[ClientID] ),
    GENERATE (
        VALUES ( ClientData[ClientID] ),
        LASTNONBLANK ( ClientData[Date], 0 )
    ),
    ClientData[Success] = "Yes"
)

If there isn't a relationship then we would need to rewrite this.

Regards,

Owen

[ThaddeusB]

---

@OwenAuger wrote:

@ThaddeusB

Thanks for testing that out!

I'm assuming the column I had been referring to as ClientData[Date] has a relationship with 'Calendar'[Date], so the slicer on the 'Calendar' table is filtering ClientData -  is that correct?

I'm just asking because your Clients Served in range measure appeared to be written as though a relationship didn't exist and is simulating a relationship using DAX.

If the relationship exists, there's nothing inherently wrong with referencing the fact table's Date column within the measure, since we are wanting to narrow down the 'Calendar'[Date] filter further and it's a bit more convenient to do that using the fact table Date column.

Given the issue with LASTDATE and duplicate dates, we can instead use LASTNONBLANK:

If there isn't a relationship then we would need to rewrite this.

Regards,

Owen

---

@OwenAuger No, there is not a relationship between calendar[date] and clientData[date].  This is because the calendar slicer interacts with two different date fields in different visualizations.  Nonetheless, I tried to create one one just the date relevant to this question as an experiment and got weird results.  (I could only select 1-to-many.  I suppose because date range in the calendar is smaller than the date range in the data.)

Using LASTNONBLANK is helpful, thanks.  I will experiment to see if I can get the manually filtered table into your function and generate the expected results...

[ThaddeusB]

@OwenAugerSo replaced the GENERATE part fo your suggestion with:

    GENERATE (
        VALUES ( Data[Client Id] ),
        LASTNONBLANK(SELECTCOLUMNS(
                FILTER (
                    Data,
                    ( clientData[Date] >= minDate && clientData[Date] <= maxDate)
                ),                 
                "ClDate",[Date]
            ),0)
    )

This seems to produce the right table.  When I, for example, count the rows directly with COUNTROWS, I get the number I would expect.  However, putting this back into your CALCULATE, or any other CALCULATE, seems to break it.  For example, If I put

CALCULATE(
    COUNTROWS(clientData),
    GENERATE (
        VALUES ( clientData[Client Id] ),
        LASTNONBLANK(SELECTCOLUMNS(
                FILTER (
                    Data,
                    ( clientData[Date] >= minDate && clientData[Date] <= maxDate)
                ),                 
                "ClDate",[Date]
            ),0)
    )
)

I get 2 instead of the 12,000+ I got counting rows directly.

Any suggestions?

[OwenAuger · Accepted solution]

Hi again @ThaddeusB

Performance-wise, it is generally best to use TREATAS to propogate filters when you have a virtual relationship, such as with Dates in your model (https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/)

I would personally write the measure something like this:

Latest Yes Count =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( ClientData[ClientID] ),
        GENERATE (
            VALUES ( ClientData[ClientID] ),
            LASTNONBLANK ( ClientData[Date], 0 )
        ),
        ClientData[Success] = "Yes"
    ),
    TREATAS ( VALUES ( 'Calendar'[Date] ), ClientData[Date] )
)

The TREATAS code in the outer CALCULATE creates a filter on ClientData[Date] equivalent to whatever selections have been made on the 'Calendar' table, Using an outer CALCULATE ensures that the inner code in green is evaluated with the "relationship" in effect.

Oh I also changed COUNTROWS to DISTINCTCOUNT(...) - it's just a little safer in case we happen to have two rows for a Client on the same date.

The above measure worked for me on your sample data. Does this work at your end?

Regards,

Owen

[Anonymous]

Hi,

I am trying to do something similar but in my case the data looks more like this:

ClientID	Date	Success	Department
1	1/2/2018	Yes	Red
1	1/21/2018	No	Red
1	2/5/2018	Yes	Blue
2	2/21/2018	No	Blue
2	3/3/2018	Yes	Blue
3	2/15/2018	Yes	Red
4	1/4/2018	Yes	Blue
4	1/8/2018	No	Red
4	1/20/2018	Yes	Red

So if I use the measure provided and try to split out the result by department, I end up double counting. For example, with the data above, setting the date filter to 1/31/2018 I get

Where, I want to just see 1 for ClientID = 4 in the Red Department as that was the latest value and all other Client IDs are No at that point.

In my head, I know I want to get LastNonBlank value irrespective of Department, which feels like doing this

Latest Yes Count = 
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( ClientData[ClientID] ),
        GENERATE (
            VALUES ( ClientData[ClientID] ),
            LASTNONBLANK ( ALL( ClientData[Date] ), 0 )
        ),
        ClientData[Success] = "Yes"
    ),
    TREATAS ( VALUES ( 'Calendar'[Date] ), ClientData[Date] )
)

But that then just gets the max client date for all Client IDs which is not appropriate, so I need to be able to allow the context of the ClientID through but ignore the context of the department from the visualisation which would normally mean some sort of ALLEXCEPT but I can't for the life of me figure out how to work that into this formula.

Any help would be hugely appreciated!

Thanks,

Rob

[ThaddeusB]

 @OwenAuger

Sorry about not replying for a while.  I had to work on other things and am only now returning to this.  I had to make one minor change to work with the actual data - stripping the time stamp off my dates - and now your solution appears to work perfectly for my data.  I'll let you know if I encounter any further difficulties.

Thank you very much for your help!

[v-lili6-msft]

hi, @ThaddeusB

When you add the condition “Data[Success]="Yes"” into your formula as below:

Clients Served in range = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN
   CALCULATE (
      DISTINCTCOUNT(Data[ClientID]),
      FILTER (
         Data,
         ( Data[Date] >= minDate && Data[Date] <= maxDate )&&Data[Success]="Yes"
      )
   )

whether it works well

If not your case, Please share your sample pbix or some sample data and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin