Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:

LeadIdIdCreatedDateTimeOldStatusNewStatus
abcabc12018.12.01 12:00:01NewCalling
abcabc22018.12.02 13:30:00CallingProposition
abcabc32018.12.03 10:00:05PropositionRegistration
abcabc42018.12.04 14:44:00RegistrationSale


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 statusCount
New0
Calling0
Proposition0
Registration1
Sale0

 
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.

 

  • Anonymous's avatar
    Anonymous
    7 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

  • Anonymous's avatar
    Anonymous
    Not 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's avatar
      AlB
      Icon for Community Champion rankCommunity Champion

      Anonymous

      Cool. Thanks for sharing.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous

    Could you share a sample data model?  It would make things easier

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi AlB, I have shared a sample file with you in the DMs.

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion

        Thanks Anonymous

        You can always share through a URL in websites like this

        so that everyone can see it

          

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity 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/409365

     

    By 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's avatar
      AlB
      Icon for Community Champion rankCommunity 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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity 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