cancel
Showing results for
Did you mean:
Regular Visitor

## 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

Accepted Solutions
Super Contributor

## Re: convert with DAX formula a digit number into a date

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.
7 REPLIES 7
Super Contributor

## Re: convert with DAX formula a digit number into a date

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.
Regular Visitor

## Re: convert with DAX formula a digit number into a date

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

Regular Visitor

## Re: convert with DAX formula a digit number into a date

Hi again @v-yulgu-msft,

Did my answer was helful ?

Super Contributor

## Re: convert with DAX formula a digit number into a date

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.
Regular Visitor

## Re: convert with DAX formula a digit number into a date

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,

Super Contributor

## Re: convert with DAX formula a digit number into a date

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.
Regular Visitor

## Re: convert with DAX formula a digit number into a date

Many Thanks Yuliana.

It worked with text format version.

All the best.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 253 members 2,937 guests
Recent signins: