Translate Excel Function into Dax

[joshua1990]

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?

[Motasem_Yakhola]

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

This 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-1

Where is my mistake?

Edit:

Cell A2 contains year number "2024".

[Greg_Deckler]

That is correct. WEEKDAY function should operate nearly identically to WEEKDAY in Excel. Same with DATE. What is the question?

[joshua1990]

How 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.