Forum Discussion
Post ProdigyTranslate Excel Function into Dax
Hello everybody!
Is there any chance to migrate these excel functions into DAX (calculated columns)?
=DATE($A2,1,1)-WEEKDAY(DATE($A2,1,1),3)+(ISOWEEKNUM(DATE($A2,1,1)-WEEKDAY(DATE($A2,1,1),3))<>1)*7
=DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3)+(ISOWEEKNUM(DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3))<>1)*7-1
=ISOWEEKNUM($C2)
In Cell A2 there is a year number.
I have already figured out that ISOWEEKNUM is in Dax WEEKNUM with type 21, right?
5 Replies
- Greg_Deckler
Community Champion
That is correct. WEEKDAY function should operate nearly identically to WEEKDAY in Excel. Same with DATE. What is the question?
joshua1990How can be built that function in Dax?
WEEKDAY(DATE($A2,1,1),3)
I can't use [Year] as $A2.
How would you replicate this?
- Greg_Deckler
So that depends, would need to understand your data layout. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But in general, you obviously can't use cell references with DAX, you have to filter down to the cell that you want using things like LOOKUPVALUE or MAXX(FILTER(...),...)
Things like that.
- Motasem_Yakhola
Helper I
You can use "what if" variable instead of "A2" Excel reference.
But what if worked only on measures; not on calculated column and i beleive it will do the job for you.
Here are the steps:
You can use Year value From Year table now easly.
Hope that answers your question.
Best wishes.
----------------------------------------------
Did I answer your question? Mark my post as a solution!
- joshua1990
Guys, thanks for your help!
I am just wondering, why I get a different date with Dax compared to the excel Function.
So, I have a calendar with a typical Date column.
This Excel function brings the 29.12.2024 as a result;
=DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3)+(ISOWEEKNUM(DATE($A2+1,1,1)-WEEKDAY(DATE($A2+1,1,1),3))<>1)*7-1This translated DAX function brings the 05.01.2025 as a result:
=Date(YEAR([Date])+1;1;1)-WEEKDAY(DATE(YEAR([Date])+1;1;1);3)+(WEEKNUM(DATE(YEAR([Date])+1;1;1)-WEEKDAY(DATE(YEAR([Date])+1;1;1);3))<>1)*7-1Where is my mistake?
Edit:
Cell A2 contains year number "2024".
