Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |