Forum Discussion

klaritak's avatar
klaritak
Frequent Visitor
4 years ago
Solved

Cumulative count on two different dates columns

Below is my data: 

IDDate CreatedDate Cancelled
101.01.2022 
201.02.2022 
315.02.2022 
401.03.2022 
503.03.2022 
627.03.2022 
701.04.2022 
801.05.2022 
902.05.202203.05.2022

 

I want to be able to show a cumulative count as below:

Cumulative count on date 27.03.20226
Cumulative count at date 03.05.2022=9-1=8

 

I want to be able to show the cumulative count based on these two dates.

 

 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi klaritak ,

    Please refer to my pbix file to see if it helps you.

    Create measures.

    Cumulative count on date 27.03 =
VAR _max_id =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date Created] = DATE ( 2022, 3, 27 ) )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] <= _max_id )
    )

    Cancelled =
VAR _created =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
VAR caleled =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date Cancelled] <> BLANK () )
    )
RETURN
    _created - caleled

    If I have misunderstood your meaning, please provide the details. Why did you want to calculate Cumulative count on date 2022.03.27.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    klaritak , Join both the dates with date table , Assume one join is inactive assume that is Date Cancelled

     

    Create measures

    Created = count(Table[ID])

    Cancelled= calculate(count(Table[ID]),USERELATIONSHIP ('Table'[Date Cancelled], 'Date'[Date]), not(isblank('Table'[Date Cancelled])) )

     

    Then create measure

    Cumm Sales = CALCULATE([Created] - [Cancelled],filter(allselected(date),date[date] <=max(date[Date])))

     

    use date from date table in visual and slicer

     

    Also check - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

    • klaritak's avatar
      klaritak
      Frequent Visitor
      4 years ago

      Hey amitchandak,

      Thank you very much for your reply.

      This formula is not working: Cancelled= calculate(count(Table[ID]),USERELATIONSHIP ('Table'[Date Cancelled], 'Date'[Date]), not(isblank('Table'[Date Cancelled])))

      I get the following error (Too many arguments were passed to the USERELATIONSHIP function. The maximum argument count for the function is 2.)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi klaritak ,

    Please refer to my pbix file to see if it helps you.

    Create measures.

    Cumulative count on date 27.03 =
VAR _max_id =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date Created] = DATE ( 2022, 3, 27 ) )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] <= _max_id )
    )

    Cancelled =
VAR _created =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
VAR caleled =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date Cancelled] <> BLANK () )
    )
RETURN
    _created - caleled

    If I have misunderstood your meaning, please provide the details. Why did you want to calculate Cumulative count on date 2022.03.27.

     

    Best Regards

    Community Support Team _ Polly

     

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