Forum Discussion

rsch91's avatar
rsch91
Helper I
1 year ago
Solved

date in table using date/time variable from another table

The company I work for has data sets for us to work with, and in one of those data sets is a certain date/time variable. Let's call it DATEX.    I want to use this variable in another table, but on...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi rsch91 ,

    Based on the description, creating a calculated column.

    FormattedDATEX = FORMAT('Table'[DATEX], "yyyy-mm-dd")

    Then, creating a new table.

    NewTable = 
SUMMARIZE(
    'Table',
    'Table'[FormattedDATEX]
)

    Besides, creating a new calculated column.

    NewCalculatedColumn = 
IF(
    NETWORKDAYS(MIN('Table'[DATEX]), NewTable[FormattedDATEX], 1, {DATE(2024,11, 6)}) < 0, 
    BLANK(),
    NETWORKDAYS(MIN('Table'[DATEX]), NewTable[FormattedDATEX], 1, {DATE(2024,11,6)})
)

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

AnalyticPulse's avatar
AnalyticPulse
Solution Sage
1 year ago

for date format you can add calculated columnn using this dax:
Formatteddate = FORMAT([datecolumn], "MM/DD/YYYY")
you will get thiss format:
11/05/2024

rsch91's avatar
rsch91
Helper I
1 year ago

Thanks! That has worked. I created a column called "formatted DATEX" Now how do I work with DATEX to create a new column in the same new table? 

 

IF(
                    NETWORKDAYS(MIN(Dim_table[DATEZ]), DATEX, 1, tabel_vakantiedagen) < 0, BLANK(),
                                NETWORKDAYS(MIN(Dim_Table[DATEZ]), DATEX], 1, tabel_vakantiedagen))
 
Right now it's working with DATEX in the date/time-format. Just want it to work with the date only. So I can't refer to "formatted  DATEX"-date column in the calculated table. How do I make sure it works with the date only in this DAX-formula? 
  • AnalyticPulse's avatar
    AnalyticPulse
    Solution Sage
    1 year ago

    rsch91 
    you can refer the new column in your dax instead of old datecolumn, it will be similar to your datetime column just that it wont have time part, only dates.

    • rsch91's avatar
      rsch91
      Helper I
      1 year ago

      The thing is, it won't let me refer to the new column in the IF-formula.