Forum Discussion

klaritak's avatar
klaritak
Frequent Visitor
4 years ago
Solved

Cumulative count on two different dates columns

Below is my data:  ID Date Created Date Cancelled 1 01.01.2022   2 01.02.2022   3 15.02.2022   4 01.03.2022   5 03.03.2022   6 27.03.2022   7 01.04.2022   ...
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.

     

     

     

     

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.)