Forum Discussion

JIGAR's avatar
JIGAR
Icon for Resolver IV rankResolver IV
5 years ago
Solved

Calculate DistinctCounts based on Max Date within the selected date range

Hello, 

 

I am trying to calculate a measure to count the distinct number of new joins, cancels and reinstates based on the max transaction date.

 

Here is the sample data. 

 

Fact Table 

MemberTransaction DateStatus
JBMonday, January 4, 2021New Join
DCSunday, January 10, 2021New Join
JBSaturday, February 6, 2021Cancel
JBMonday, February 8, 2021Reinstate
DCWednesday, February 10, 2021Cancel

 

Fact table is connected to Dim Date table 

 

What I am trying to achieve is 

 

For example : 

 

If I select a date range from Jan 1 2021 to Jan 5 2021

 

New Joins (#) = 1 (Only JB has new join status)

Cancel (#) = 0 (None of the member has cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Jan 31 2021

 

New Joins (#) = 2 (JB and DC both have new join status)

Cancel (#) = 0 (None of the member has cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Feb 7 2021

 

New Joins (#) = 1 ( This is member DC and not JB)

Cancel (#) = 1 (Since JB now is on cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Feb 28 2021

 

New Joins (#) = 0 (None of the member is on new join status now)

Cancel (#) = 1 (Since DC now is on cancel status)

Reinstate (#) = 1 (Since JB now is on reinstate status)

 

I hope I was able to explain the question.

 

Any help here is greatly appreciated. 

 

Thank you

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi JIGAR ,

    Try the following formula:

    Result = 
var _Table = 
    FILTER(
        ALLSELECTED('Fact Table'), 
        'Fact Table'[Multiplier] = 1 
        && 'Fact Table'[Member] = MAX('Fact Table'[Member]) 
    )
var _Date = 
    MINX(
        _Table,
        'Fact Table'[Transaction Date]
    )
return 
    COALESCE(
        CALCULATE(
            MAX('Fact Table'[Status]),
            FILTER(
                _Table,
                'Fact Table'[Transaction Date] = _Date
            )
        ),
        "NA"
    )

    This is my PBIX file.

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EefZ93NSdIZOrajS29WnBwIBG73Q1RwgF17t5_ngWcux7w?e=01Lkn5

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • JIGAR's avatar
    JIGAR
    5 years ago

    v-kkf-msft : This thing worked really well. Thank you very much.

     

    However, how do I calculate the count of members on each status. 

     

    So going with the same example above : 

     

    For this date range and result 

     

    How can I show the count 

     

    Reinstate = 1

    New Join = 0

    Cancel = 0

     

    This count should change based on the date range selected and also retaining the above logic. 

     

    Can you please help me with that DAX logic ? 

     

    Thank you 

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi JIGAR ,

     

    Try the following formula:

    Measure = 
var _count=
    IF(
        MAX('Fact Table'[Status]) = [Result],
        COUNT('Fact Table'[Multiplier])
    )
var total = 
    CALCULATE(
        DISTINCTCOUNT('Fact Table'[Member]),
        FILTER(
            'Fact Table',
            [Result] <> "NA"
        )
    )
return 
    IF(
        ISFILTERED('Fact Table'[Status]),
        _count,
        total
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    JIGAR , Create a measure like

    Measure 7 = Sumx(SUMMARIZE(Table3,Table3[Member],"_1",if(CALCULATE(max(Table3[Status]), filter(Table3, Table3[Transaction Date] =CALCULATE(max(Table3[Transaction Date]), filter(ALLSELECTED(Table3), Table3[Member] =max(Table3[Member]))))) ="New Join",1,0)),[_1])

     

    • JIGAR's avatar
      JIGAR
      Icon for Resolver IV rankResolver IV
      5 years ago

      Thank you so much amitchandak 

       

      This logic worked perfectly okay until I tried to plot these measures on a line chart/column chart. 

       

      While plotting these measures on a line chart, its double counting the members. 

       

      For a date range 1 Jan 2021 to 28 Feb 2021, 

       

      JB is now in reinstate state

      DC is now in cancel state. 

       

      The screenshot below shows that there are 2 members in New Joins and 1 each in reinstate and cancel. 

       

       

      The KPI cards on the tops shows the correct numbers. 

       

      Any idea how do I handle this ? 

       

      Thank you again for your help ðŸ˜Š

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi JIGAR ,

        Try the following formula to create two measures:

        RecentDate = 
CALCULATE(
  MAX( 'Fact Table'[Transaction Date] ), 
  FILTER( 
    ALLSELECTED( 'Fact Table' ), 
    'Fact Table'[Member] = MAX( 'Fact Table'[Member] )
  )
)
        MemberCount = 
VAR MemberSegment = 
    FILTER(
        ALLSELECTED('Fact Table'),
        VAR NewDate = [RecentDate]
        VAR SegmentForMember = 
            FILTER (
                'Fact Table',
                'Fact Table'[Member] = 'Fact Table'[Member]
                &&'Fact Table'[Transaction Date] = NewDate
            )
        VAR IsMemberInSegments = NOT ISEMPTY(SegmentForMember) 
        RETURN IsMemberInSegments 
    )
VAR Result = 
    CALCULATE (
        COUNTROWS( 'Fact Table' ),
        KEEPFILTERS(MemberSegment)
    )
RETURN COALESCE(Result,0)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.