Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbrechet
Helper I
Helper I

convert with DAX formula a digit number into a date

Hello Hello,

 

Dear PBI community, I would need your held on that one ......

 

Does somebody knows how to convert with DAX formula a digit number into a date ?
Not entire column

Let me explain:

 

I am trying to show on a table sales of last weeks and by days when currents week.

 

I have the below formula which works fine, especially for weeks, but shows day as number (example 42920)

fx= IF(Append1[Week]=WEEKNUM(TODAY());Append1[Date of sales];Append1[Week])

Is there a way to keep "date of sales" in date format ?

 

Many thanks and take care

1 ACCEPTED SOLUTION

Hi @rbrechet,

 

Per my understanding, the workaround that setting both [Date of sales] and [Week] to text data type can be a possible option. In a single column, you can have values like '07/17/2017', '07/18/2017', '1', '2' etc. But all of them are text values.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

Hi @rbrechet,

 

The values for Append1[Date of sales] are numeric formatted as "42920", right?

 

To convert this value to date format, you could refer to this formula:

fx= IF(Append1[Week]=WEEKNUM(TODAY());DATE(1900,1,1)+Append1[Date of sales]-1;Append1[Week])

 

If I have something misunderstood, please illustrate your requirement with sample data.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, THANKS A LOT  for you asnswer.

 

Values for Apped1[Date of sales] are date formatted, this is why I originally though to have a "copy" of those date as result.

 

 

"Date of sales" is basid date formated column

"week" is weeknum function based on date format

 

Hope this help

 

Hi again @v-yulgu-msft,

Did my answer was helful ?

 

Many thanks for your time 🙂

Hi @rbrechet,

 

What is the digit number in your scenario? The returned value for Append1[Date of sales] in this formula: fx= IF(Append1[Week]=WEEKNUM(TODAY());Append1[Date of sales];Append1[Week])?

 

If your requirement is to keep [Date of sales] with date format, while [Week] is digit number in a single column returned by your fx formula, this is not possible. One column can only be set to one data type.

 

If I have something misunderstood, please illustrate your requirement with sample data.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks again for your answer.

No you understood it good.

 

Ok I see it won't me possible to  have date format + digit numer for week.


What about to use text format: 

- Add column wich will say monday / thuesday ... for 07/17/2017 - 07/18/2017....

- And then refer to this new column instead of [Date of sales]

- And move [Week] into text format

 

Or something like that.

If no, fine, I will find something else

 

Best,

 

 

Hi @rbrechet,

 

Per my understanding, the workaround that setting both [Date of sales] and [Week] to text data type can be a possible option. In a single column, you can have values like '07/17/2017', '07/18/2017', '1', '2' etc. But all of them are text values.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Many Thanks Yuliana.

It worked with text format version.

All the best.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.