Forum Discussion
Final client status by grouping on selected date range
Hi,
I am fairly new Power BI user, currently working on interesting piece of DAX logic, which should not cause too much hasle, but I feel like I missing something in my code and cannot find out what exactly.
I have data on client movement through different sales stages which structured like this:
| LeadId | Id | CreatedDateTime | OldStatus | NewStatus |
| abc | abc1 | 2018.12.01 12:00:01 | New | Calling |
| abc | abc2 | 2018.12.02 13:30:00 | Calling | Proposition |
| abc | abc3 | 2018.12.03 10:00:05 | Proposition | Registration |
| abc | abc4 | 2018.12.04 14:44:00 | Registration | Sale |
I have to represent this data by slowing the max status the client has achieved for any selected date range. E.g. if I select 12.02 to 12.03, I would want the matrix table to add a count to Registration counter like this:
| Final status | Count |
| New | 0 |
| Calling | 0 |
| Proposition | 0 |
| Registration | 1 |
| Sale | 0 |
Here is the current code that I tried using:
Measure :=
COUNTROWS (
FILTER (
'Raw LH',
GROUPBY (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE (
'Raw LH',
FILTER ( DateTable, DateTable[Date] >= MIN ( DateTable[Date] ) ),
FILTER ( DateTable, DateTable[Date] <= MAX ( DateTable[Date] ) )
),
'Raw LH'[LeadId],
'Raw LH'[NewStatus],
'Raw LH'[CreatedDateTime],
'Raw LH'[Id]
),
"MaxDate", CALCULATE (
MAX ( 'Raw LH'[CreatedDateTIme] ),
FILTER ( 'Raw LH', 'Raw LH'[LeadId] = EARLIER ( 'Raw LH'[LeadId] ) )
)
),
'Raw LH'[CreatedDateTIme] = [MaxDate]
),
'Raw LH'[Id]
)
)
)
In this code, If I understand correctly, I am selecting the data for the selected range, then grouping on the basis of LeadId, interaction Id, InteractionTime and the newStatus. I am also adding another column for the max date. I then select only the rows with max date, and group again by unique event identifier (Id).
As much as I have tested this code, the relative date filtering seems to be working, so does grouping and selecting the latest, thus the maximum, interaction, however when run this measure in the matrix table, it adds count for all interactions during the select time period.
I hope this makes sense and I would greatly appreciate if you could help me with this. I feel like I am so close to getting it right, but as much as try I can't crack this.
Thanks,
Arturs.
- Anonymous7 years ago
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.
19 Replies
- AnonymousNot applicable
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.
- AlB
Community Champion
Anonymous
Cool. Thanks for sharing.
- AlB
Community Champion
Hi Anonymous
Could you share a sample data model? It would make things easier
- v-lili6-msft
Community Support
hi, Anonymous
You maybe take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365By the way, for CreatedDateTime is a datetime column with different time,
and Date table is only a datetime column with 12:00:00 AM.
So you‘d better use this formula to create a new CreatedDateTime
New CreatedDateTime = 'Raw LH'[CreatedDateTime].[Date]
then use this new date column for calculation.
Hope these can help you.
Best Regards,
Lin
- AlB
Community Champion
Hi v-lili6-msft
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
- v-lili6-msft
Community Support
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