Planned vs. actual not working properly

[nikkirai]

Hi all, 

I have a table containing Planned Dates and Actual Dates for different quarters.  I wish to include cumulative counts for Planned Dates and Actual Dates categorized by different quarters in the same visual as shown below. However, I was only able to get either cumulative planned or cumulative actual but not both. Is there something that I am missing? I included my dax codes below. Thank you so much!

cumplanned =
calculate(count('archive'[Customer ID]),
filter(ALLSELECTED('archive')
, Max('archive'[planned date]) >= 'archive'[planned date])
, values(Datetable[YearQuarter]
))

cumactual =
calculate(count('archive'[Customer ID]),
filter(ALLSELECTED('archive')
, ('archive'[actual date] <= Max('archive'[actual date]
, values(Datetable[YearQuarter])
))))

[amitchandak]

@nikkirai , join both Dates with date of date table. On e join will be inactive, activate that using userealtionship

Assume Actual is inative

**bleep**  planned =
calculate(count('archive'[Customer ID]),
filter(ALLSELECTED('Date')
, 'Datetable'[Date] <= Max(Datetable[Date])

))

**bleep** actual =
calculate(calculate(count('archive'[Customer ID]),userelationship(Datetable[Date], 'archive'[actual date]))
filter(ALLSELECTED('Date')
, 'Datetable'[Date] <= Max(Datetable[Date])

))

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[nikkirai]

Hi @amitchandak,
Thank you for your help. I did as you advised and below is the result. The cumulative count seems to be for all quarters. Is there any way to separate them?

Thank you so much!

[gmsamborn]

Hi @nikkirai 

Unless I'm missing something, this seems pretty straight-forward.

Planned QTD = 
CALCULATE(
    COUNTROWS( 'archive' ),
    USERELATIONSHIP( 'archive'[planned date], 'Date'[Date] ),
    DATESQTD( 'Date'[Date] )
)

Actual QTD = 
CALCULATE(
    COUNTROWS( 'archive' ),
    USERELATIONSHIP( 'archive'[actual date], 'Date'[Date] ),
    DATESQTD( 'Date'[Date] )
)

In my example, I left both relationships as Inactive.

nikkirai.pbix

Please let me know if I'm misunderstanding something.