Calculated Columns UseRelationship Different Dates Return Same Weeks

[UncleLewis]

Hi all,

I have 4 different date fields I joined to coporate calendar
I created Calculated Columns for each to return the Fiscal Week
However, when I review the data in the calculated columns, they all show the same week even though the dates are different.
For example I see a Start Date of 3/3/2022 and a Completion Date of 3/21/2022 but both are returning Week 12
The week returned in this example is Week 10 which is correct for 3/3/2022, but 3/21/2022 should return Week 13.

Here are 2 of the DAX Formulas I am using in the Calc'd Columns:

FWeek_Start = CALCULATE(VALUES( Fiscal_Calendar[FiscalWeek] ), USERELATIONSHIP ( Fiscal_Calendar[Date], fact[Start_Date]))
FWeek_Complete= CALCULATE(VALUES( Fiscal_Calendar[FiscalWeek] ), USERELATIONSHIP ( Fiscal_Calendar[Date], fact[Complete_Date]))

Thanks,
-w

[UncleLewis · Accepted solution]

I was able to resolve this with this Calculated Column Formula from the team at Enterprise DNA

Max Date =
Var CurrentDate = Fact[Start_Date]

Return
MAXX(
     FILTER{ALL(FiscalCalendar, FiscalCalendar[Date] = CurrentDate),
          FiscalCalendar[FiscalWeek])

Thanks,
-w

[UncleLewis]

Thanks amitchandak,

That appears to be returning an error complaining that there are too many arguments in the Filter Function

It seems FILTER justs wants : FILTER(<table>,<filter>) 
But your solution seems to have a 3rd argument?

Thanks,
-w

[amitchandak]

@UncleLewis , Create new columns like

FWeek_Start = maxx(filter(Fiscal_Calendar, Fiscal_Calendar[Date], fact[Start_Date]), Fiscal_Calendar[FiscalWeek] )

FWeek_Complete= maxx(filter(Fiscal_Calendar, Fiscal_Calendar[Date], fact[Complete_Date]), Fiscal_Calendar[FiscalWeek] )

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8