Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

broken relation with date dax table

Hello,

 

I have a dax table as follow

Date = CALENDAR(DATE(2018,1,1),DATE(2030,12,31))

 

And a fact table with sales and report repiod (multiple with same time/date).

 

I made a link one to many but it seems don't work.

 

If i call values from calendar it doens't compute values (report time).

 

Whant i can do to fix this ?

 

Regards.

Date
  • MattAllington's avatar
    MattAllington
    5 years ago

    You really need to post an image of your model. You should not need USERELATIONSHIP. It is not clear if [report period] is a date. You should not be using the fact column inside SAMEPERIODLASTYEAR

  • Anonymous's avatar
    Anonymous
    5 years ago

    I finaly added a segment with the Report[Report Period] and it works MattAllington  !

    I wonder how ? Did it regroupe the report ?

5 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Split the date/time column in the fact table into 2 columns, one for date, one for time. Then join on date

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Matt,

    It doens't works even if i force the relas if i force this column usage.

    I have simply changed the date on both sides to DD/MM/YYYY to test it.


    Maybe i am doing wrong ? Total YTD LY return me an error with the following code:

    Total YTD =
    CALCULATE (
    SUM(Report[Revenue USD]),
    DATESYTD ( Report[Report Period] ),
    USERELATIONSHIP (Report[Report Period],'Date'[Date] )
    )

    Total YTD LY =
    CALCULATE (
    [Total YTD],SAMEPERIODLASTYEAR(Report[Report Period] ),
    USERELATIONSHIP (Report[Report Period],'Date'[Date] )
    )

    Error Message:
    MdxScript(Model) (56, 17) Erreur de calcul dans la mesure 'Report'[Total YTD LY] :
    La fonction « SAMEPERIODLASTYEAR » attend une sélection contiguë quand la colonne de date n'est pas unique, est discontinue ou contient une partie heure.


    Regards.

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      5 years ago

      You really need to post an image of your model. You should not need USERELATIONSHIP. It is not clear if [report period] is a date. You should not be using the fact column inside SAMEPERIODLASTYEAR

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you for your reply,

         

        i modified the code for both measures but i have the same amount for Total YTD LY and Total YTD (i should have less for YTD LY):

        Total YTD =
        CALCULATE (
        SUM(Report[Revenue USD]),
        DATESYTD ( Report[Report Period] )
        )
        Total YTD LY =
        CALCULATE (
        [Total YTD],SAMEPERIODLASTYEAR('Date'[Date] )
        )

        (I put some blank on other fields)

         

         

         

         

         

         

         

         

         

         

         

         

        Report period (each client has his report with the same report period for each month):

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Regards