cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbrechet Regular Visitor
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
v-yulgu-msft Super Contributor
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
v-yulgu-msft Super Contributor
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.
rbrechet Regular Visitor
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

 

rbrechet Regular Visitor
Regular Visitor

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

Hi again @v-yulgu-msft,

Did my answer was helful ?

 

Many thanks for your time Smiley Happy

v-yulgu-msft Super Contributor
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.
rbrechet Regular Visitor
Regular Visitor

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

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 Super Contributor
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.
rbrechet Regular Visitor
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.

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 253 members 2,937 guests
Please welcome our newest community members: