Calendar that links two date columns so that calculated measures can be displayed in a single visual

[Chris2016]

Hello, 

I need help with the following issue.

I have a table that contains two date columns - one for Task Assigned and one for Task Completed:

NameTaskActiveAssigned DateCompleted date_FYAssigned_FYCompletedCompletionPercentage

A	no1	True	11/09/2022	10/10/0202	FY2023	FY203	100
B	no1	True	05/02/2022	06/10/2022	FY2022	FY2022	100
C	no2	True	10/12/2021	10/01/2022	FY2022	FY2023	100
D	no2	True	02/15/2022	04/10/2022	FY2022	FY2022	100
A	no3	False	01/02/2020	02/04/2020	FY2020	FY2020	100
B	no2	True	04/07/2022		FY2022	FY	54.32098765432
D		True	04/03/2022		FY2022	FY	92.307692307692
F	no1	True	05/10/2022		FY2022	FY	1.666666666666
G	no2	True			Missing Assigned Date	FY	53.086419753086
H	no1	True		10/05/2022	Missing Assigned Date	FY2023	100
I	no4	True			Missing Assigned Date	FY
J	no4	True		11/05/2021	Missing Assigned Date	FY2022	100
K	no3	False			Missing Assigned Date	FY

Some measures and columns are calculated based on each of the date columns. Since I can only create a relationship with the Calendar table for one of the date columns ("Assigned date"), is there a way to create a calendar calculation that will allow me to display the calculated columns and measures for both Assigned and Completed in the same visual? I would need that calculation to use instead of the FY and FQ in the Calendar table, which now filters based on the relationship created with "Assigned date".

Power BI sample file here: https://www.transfernow.net/dl/20221114Jb5N9v4p

Thanks a lot for any help!

[v-jialluo-msft · Accepted solution]

^{Hi @Chris2016 ,}

^{Please follow these steps:}

^{(1) Create a new relationship}

^{(2) Create an index to prevent data aggregation}

^{(3) Create measures}

new complete date =
IF (
    ISINSCOPE ( 'Table'[Index] ),
    CALCULATE (
        MAX ( 'Table'[Completed date] ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Completed date] )
    )
)


#newCompleteUsers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    'Table'[Active] = TRUE (),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Completed date] )
)


#newCompleteTasks =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Task] ),
    'Table'[Active] = TRUE (),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Completed date] )
)

^{(4)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Chris2016]

Hi, Gallen,

Thanks a lot, this helps. 
I used ISINSCOPE for the calendar FY in order to get rid of the index: 

#new complete date = 

IF (

    ISINSCOPE ( 'Calendar'[FY] ),

    CALCULATE (

        MAX ( 'Table'[Completed date] ),

        USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Completed date] )

    )

)

And this gives me what I need:

Thanks again!

[amitchandak]

@Chris2016 , refer if this blog or attcahed file can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...