Forum Discussion
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 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.
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.
8 Replies
have you tried using format function for date format?
I am an absolute newbie to DAX, could you show me how to use it? Thank you.
for date format you can add calculated columnn using this dax:
Formatteddate = FORMAT([datecolumn], "MM/DD/YYYY")
you will get thiss format:
11/05/2024Thanks! 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?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.
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.
