Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nikkirai
Frequent Visitor

Planned vs. actual not working properly

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!

nikkirai_4-1695921457298.png

nikkirai_2-1695920575902.png

 

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

 


 



 

3 REPLIES 3
amitchandak
Super User
Super User

@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?

nikkirai_0-1695960101636.png

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.

nikkirai.pbix

 

Please let me know if I'm misunderstanding something.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.