date in table using date/time variable from another table

[rsch91]

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 only the date portion, not the time. So right now my DAX sort of looks like this:

SUMMARIZE(

        VARIABLEZ

        DATEX)

But: it's showing the date and time for DATEX, since it's a date/time variable. I can't change the data type in the source (DATEX), my company has disallowed this. How do I put DATEX in the table using only the date?

Thank you. 

[Anonymous · Accepted solution]

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]

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]

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]

@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]

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

[AnalyticPulse]

why?

[AnalyticPulse]

have you tried using format function for date format?

[rsch91]

I am an absolute newbie to DAX, could you show me how to use it? Thank you.