March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
157 | |
97 | |
79 | |
69 |