Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Dax time intelligence functions not working

Reading from Sage I have created in my transactions table a calculated column which contcatonates the month and last two digits of the year to make a code.   I then link them to a calnder table whi...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

    Please help to post sample data and your expected result. If you want to use DATEVALUE function and CONCATENATE function in Power BI Desktop, you can review the following example.

    Right click you table and choose “New column”, then you can write the following formula to return date values.

    Column = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(Table1[Month],"/"),CONCATENATE(Table1[Day],"/")),Table1[Year]))

    Column1 = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(Table1[Month],"/"),CONCATENATE("01","/")),Table1[Year]))


    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,

Based on the above information, it is difficult for us to analysis the cause of this issue.

Could you please help to post the sample data of the transactions table and calendar table? Also we need to know which formulas you use to create the calculated column and DAX expression that references date field. In addition, do you create relationship between the transactions table and calendar table? If so, which columns do you use in the two tables to link each other?



Thanks,
Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks lydia,

 

I was using a text field to link the two together, but it seems that if you want to use DAX intelligence functions you need to use date links?

 

Thanks

 

Chris

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

    Could you please post sample data of your tables? And we also need to know the detailed DAX formulas that you use and how you create the relationship between tables. We will appreciate it if you can post relevant screenshots.

    Thanks,
    Lydia Zhang

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

       

      Thanks for getting back,  I was using calcode to link the tables which is whole number made up of year and month combined and in each row I have the relevent date that it refers to.

       

      I have thought though about converting the date time to a date and then link it that way but not sure of the syntax.

       

      Something like....

       

      Column = DATEVALUE("01/"& CONCATENATE(Transactions[trans_period],"/"&Transactions[nlyear]))))

       

      But that doesnt work.

       

      Any help appreciated.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Anonymous,

        Please help to post sample data and your expected result. If you want to use DATEVALUE function and CONCATENATE function in Power BI Desktop, you can review the following example.

        Right click you table and choose “New column”, then you can write the following formula to return date values.

        Column = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(Table1[Month],"/"),CONCATENATE(Table1[Day],"/")),Table1[Year]))

        Column1 = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(Table1[Month],"/"),CONCATENATE("01","/")),Table1[Year]))


        Thanks,
        Lydia Zhang