Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date Table - Use Relationship - Weekday, Weeknum etc.

Hello,

 

i have a fact table with two date columns (Start Date and End Date). I also have a date table with dates, years, months, weekdays etc. Now i want to create a table visual with the two date columns which should also show the weekdays. When I drag the weekday column from the date table in the table visual it shows the weekdays from Start Date because thats the active relationship between the two tables. But i also want to have the weekdays from End Date. How can I do that? I tried something with use relationship but it doesnt work:

 
Calculate(Max(Date_Table[Weekday]), USERELATIONSHIP(Date_Table[Date],Fact_Table[Date End]))

 

Can somebody please help me?

 

Need Help
  • sturlaws's avatar
    sturlaws
    4 years ago

    Hi, Anonymous,

     

    you could try to write your measure like this:

    MEASURE endWEEKDAY =
    VAR _enddate =
        CALCULATE ( SELECTEDVALUE ( sales[enddate] ) )
    RETURN
        CALCULATE (
            MAX ( Dates[weekday name] ),
            FILTER ( ALL ( Dates ), Dates[Date] = _enddate )
        )

     

    cheers,

    Sturla

2 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    4 years ago

    Hi, Anonymous,

     

    you could try to write your measure like this:

    MEASURE endWEEKDAY =
    VAR _enddate =
        CALCULATE ( SELECTEDVALUE ( sales[enddate] ) )
    RETURN
        CALCULATE (
            MAX ( Dates[weekday name] ),
            FILTER ( ALL ( Dates ), Dates[Date] = _enddate )
        )

     

    cheers,

    Sturla