Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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 @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
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
Many Thanks Yuliana.
It worked with text format version.
All the best.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |