topic Re: Remove no of days from Date with DAX in DAX Commands and Tips

Remove no of days from Date with DAX

Anonymous — Tue, 27 Dec 2022 07:14:33 GMT

Hi All,

The requirement is i have to remove days(Time) from Date column with DAX(calculated column or measure)
Time Column - Text
Date Column - Date(dd-mm-yy)

Example - if the Time column has 2,00 days, Date colum (17th Jan 22) = 17th jan 22-3days
the output should come - 15th Jan 22
Any advise please?

Thanks

Re: Remove no of days from Date with DAX

ValtteriN — Tue, 27 Dec 2022 07:33:33 GMT

Hi,

You can use e.g. MAX/SELECTEDVALUE combined with a simple subtraction:

dax:

Measure 30 =

var _date = MAX('Table (19)'[Column1])

var _diff = MAX('Table (19)'[Column2])

return

_date - _diff

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Remove no of days from Date with DAX

Anonymous — Tue, 27 Dec 2022 11:27:18 GMT

Hi ValtteriN
I have tried your formula - Getting incorrect result as below screenshot
Pleae advise further.

Thanks

Re: Remove no of days from Date with DAX

ValtteriN — Tue, 27 Dec 2022 11:58:52 GMT

I see,

the issue is with the format ",00" the most direct way to solve this would be to change the columns data type to number. Here is an dax alternative:

Measure 30 =

var _date = MAX('Table (19)'[Column1])

var _diff = value(max('Table (19)'[Column2]))/100

return

_date-_diff

The 14,00 is converted for example to 1400 with VALUE and this is why your numbers don't match.

Re: Remove no of days from Date with DAX

Anonymous — Tue, 27 Dec 2022 13:33:48 GMT

Hi ValtteriN

Thanks for the solution. But can we convert same measure to calculated column?
beacuse when i am creating Measure . i am getting circular dependency error (if we use combination of more calcu,ated columns and measures their might be circular dependency) to avoid can we convert same measure to calculated column ??

i have tried to convert same into calculated column but it is giving incorrect result

Please advise