convert with DAX formula a digit number into a date

[rbrechet]

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

[v-yulgu-msft]

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

[rbrechet]

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

[rbrechet]

Hi again @v-yulgu-msft,

Did my answer was helful ?

Many thanks for your time 🙂

[v-yulgu-msft]

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

[rbrechet]

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,

[v-yulgu-msft · 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

[rbrechet]

Many Thanks Yuliana.

It worked with text format version.

All the best.