cancel
Showing results for
Did you mean:
Frequent Visitor

## Re: Final client status by grouping on selected date range

Thanks you @AlB for the take on this challenge! As you wrote, your measure shows the last status only for the whole period, which is the reason why my approach was to start by grouping the values. I am not sure how to solve this tricky selection part though..

Super User

## Re: Final client status by grouping on selected date range

@Arturs

Have you tried to use a Date slicer? It should work with that. It seems to be working for me.

Super User

## Re: Final client status by grouping on selected date range

I am quite curious about the

`'Raw LH'[CreatedDateTime].[Date]`

that you show as I had not seen it before. So you can access the components of date-type data with than syntax? Is this syntax used in other ways too or with other data types?  Can I read about it somewhere?

Is the above actually a shortcut for:

`DATE(YEAR(Raw LH'[CreatedDateTime]),      MONTH(Raw LH'[CreatedDateTime]),     DAY(Raw LH'[CreatedDateTime]))`

Thanks very much

Community Support Team

## Re: Final client status by grouping on selected date range

hi, @AlB

Yes, you could use this formula must before create a relationship with a date table.

, of course, you could use your formula,

`DATE(YEAR(Raw LH'[CreatedDateTime]),      MONTH(Raw LH'[CreatedDateTime]),     DAY(Raw LH'[CreatedDateTime]))`

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: Final client status by grouping on selected date range

Thanks @v-lili6-msft

Is thet syntax that you provide, with the dot, only available for dates  or in others cases as well? That's what I was most curious about.

Frequent Visitor

## Re: Final client status by grouping on selected date range

@AlBThe measure your provided shows the max status for the whole period. If date slicer de-selects this max interaction, it will disapear and will not get recalculated. Maybe I am bad at explaining what I want to achieve, so let me illustrate - we have a client with 3 interactions:

 Client InteractionID Date OldStatus NewStatus xyz xyz1 01.dec A B xyz xyz2 03.dec B C xyz xyz3 05.dec C D

If the date slicer has selected 12.01 - 12.05, the max interaction in this range is xyz3 and the respective max status = "D", therefore I need the output to be like this:

 Measure Value B 0 C 0 D 1

However, now suppose I adjust date slicer to 12.01 - 12.04 The max interaction in this range is xyz2 and the respective max status = "C", therefore I need the output to look like this:

 Measure Value B 0 C 1 D 0

This is what I am trying to achieve. I think that your measure is close to what I need, but I lack the knowledge to tweak it.

Super User

## Re: Final client status by grouping on selected date range

Hey @Arturs what's up

I haven't yet had time to look in detail at your latest post but what is the status on this? Have you made any progress?

Frequent Visitor

## Re: Final client status by grouping on selected date range

Hi @AlB, I haven't had time to work on this recently. Should I happen to find the solution, I will definitely share it.

Frequent Visitor

## Re: Final client status by grouping on selected date range

Hello all,

I have found solution to my problem. Here is the code:

```CountOfMaxStatus = COUNTROWS(
FILTER (
TestData;
TestData[TimeStamp]
= CALCULATE (
MAX ( TestData[TimeStamp] );
FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client]));
FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp]));
filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp]))
)
))```

Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure

Thanks @AlB and @v-lili6-msft for the help.

Super User

## Re: Final client status by grouping on selected date range

@Arturs

Cool. Thanks for sharing.