Data rolling over to next year if status not completed

[GA1993]

Hi,

I would like to display the Rate values as displayed on the Expectation tables where December 20 2022 rolls over as status was not completed. My code, however, displays the table with the Actual results below.

Weirdly, data rolls over if it is on the same year (eg below November to December) but it doesn't for December 2022 to January 2023.

Expectation:

Date	Target Completion	KPI	Rate
22-Nov	December 15 2022	1	100%
22-Nov	December 15 2022	1
22-Nov	December 20 2022	1
22-Dec	December 20 2022	0	50%
22-Dec	January 21 2022	1
23-Jan	December 20 2022	0	0%

Actual:

Date	Target Completion	KPI	Rate
22-Nov	December 15 2022	1	100%
22-Nov	December 15 2022	1
22-Nov	December 20 2022	1
22-Dec	December 20 2022	0	50%
22-Dec	January 21 2022	1
23-Jan	blank	1	100%

CALCULATE 
  (
   COUNTROWS('MP005 (Time to Hire)'),
   FILTER('MP005 (Time to Hire)',
   'MP005 (Time to Hire)'[Target Completion]>='MP - Engine'[Date]
    && 'MP005 (Time to Hire)'[Target Completion]<=EOMONTH('MP - Engine'[Date],0)
    && 'MP005 (Time to Hire)'[KPI]=1)                
   )
/ (
(CALCULATE
  (
   COUNTROWS('MP005 (Time to Hire)'),
    FILTER('MP005 (Time to Hire)',
    'MP005 (Time to Hire)'[Target Completion]>='MP - Engine'[Date]
     && 'MP005 (Time to Hire)'[Target Completion]<=EOMONTH('MP - Engine'[Date],0)
     && 'MP005 (Time to Hire)'[KPI]=1)                                    
   )
) + 
(CALCULATE
 (
    COUNTROWS('MP005 (Time to Hire)'),
    FILTER('MP005 (Time to Hire)',
    'MP005 (Time to Hire)'[Target Completion]>='MP - Engine'[Date]
     && 'MP005 (Time to Hire)'[Target Completion]<=EOMONTH('MP - Engine'[Date],0)
     && 'MP005 (Time to Hire)'[KPI]=0)                                    
 )
)   
)

Can someone help me please? Thanks in advance!

[Anonymous · Accepted solution]

Hi @GA1993,

I think the EOMonth function may not suitable for the cross year calculation, you can try to use the following dax formula if it helps:

formula =
CALCULATE (
    DIVIDE (
        CALCULATE (
            COUNTROWS ( 'MP005 (Time to Hire)' ),
            'MP005 (Time to Hire)'[KPI] = 1
        ),
        CALCULATE (
            COUNTROWS ( 'MP005 (Time to Hire)' ),
            'MP005 (Time to Hire)'[KPI] IN { 0, 1 }
        )
    ),
    FILTER (
        'MP005 (Time to Hire)',
        'MP005 (Time to Hire)'[Target Completion] >= 'MP - Engine'[Date]
            && 'MP005 (Time to Hire)'[Target Completion]
                <= DATE ( YEAR ( 'MP - Engine'[Date] ), MONTH ( 'MP - Engine'[Date] ) + 1, 1 ) - 1
    )
)

Regards,

Xiaoxin Sheng

[GA1993]

Thanks for the reply.

I wanted to know more about your code what does the following mean?

MONTH ( 'MP - Engine'[Date] ) + 1, 1 ) - 1