Cumulative Count by Date - Actual vs Planned

[Anonymous]

Hello,

I am new to the power Bi world and hoping I can get some guidance in terms of calculating the count of dates for planned vs actual by month to allow me to plot both lines in a S-Curve

I have included the sample dataset I am using as well as my .pbix file 

I was able to get the S curve the way want. But, the problem I am facing is the count of actuals by month is not correct on the visual. For example there should be 3 closed in Feb, but the visual isn't showing that. I seems to think that's because the actual date is before the planned date. But, I struggling to find a way aaround.

Any help you can provide is much appreciated.

[CNENFRNL]

Hi, @Anonymous , you might want to adjust the calculated column sheet1[sOFm]

sOFm = ENDOFMONTH( Sheet1[Planned Date] )

then measures

cumplanned =
COUNTROWS ( FILTER ( ALL ( Sheet1 ), Sheet1[sOFm] <= MAX ( Sheet1[sOFm] ) ) )

===================================

cumactual =
COUNTROWS (
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Actual Date] <= MAX ( Sheet1[sOFm] )
            && Sheet1[Actual Date] > 0
    )
)

[Anonymous]

@CNENFRNL Thank you for your suggestion. It did improve the issue. But, it is still not showing the 2 that was completed in Feb.

[wdx223_Daniel]

[wdx223_Daniel]

[Anonymous]

Thank you for your help, but I think the numbers are still off based on what I can tell from doing in excel. Aug actual should be 8, its still showing 6

[wdx223_Daniel · Accepted solution]

the better way is to create a date table, and create relationships between each date column to date table's date, then put dates on the x axis and use USERELATIONSHIP function to switch the relationship.

[voodoo23]

Hello, can you explain how do it? I have same problem. I have planned dates and completed dates ( actual ) and i have date table. What i need to do? i create relationship between actual dates with date table. what next?

[Anonymous]

@wdx223_Daniel thank you! the USERELATIONSHIP function solved it along with creating the seperate date table.

[Anonymous]

@wdx223_Daniel , thank you I will give the userrelationship a try