Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
))))
@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
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!
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.
Please let me know if I'm misunderstanding something.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |