Forum Discussion

UncleLewis's avatar
UncleLewis
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Calculated Columns UseRelationship Different Dates Return Same Weeks

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



 

Need Help
  • UncleLewis's avatar
    UncleLewis
    4 years ago

    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

3 Replies

  • UncleLewis's avatar
    UncleLewis
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    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

     

  • UncleLewis's avatar
    UncleLewis
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    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