topic Re: Translate Excel Function into Dax in DAX Commands and Tips

Translate Excel Function into Dax

joshua1990 — Thu, 16 Apr 2020 15:34:25 GMT

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?

Re: Translate Excel Function into Dax

Greg_Deckler — Thu, 16 Apr 2020 15:41:15 GMT

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

Re: Translate Excel Function into Dax

joshua1990 — Thu, 16 Apr 2020 15:46:17 GMT

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?

Re: Translate Excel Function into Dax

Greg_Deckler — Thu, 16 Apr 2020 16:02:42 GMT

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.

Re: Translate Excel Function into Dax

Motasem_Yakhola — Fri, 17 Apr 2020 16:23:43 GMT

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!

Re: Translate Excel Function into Dax

joshua1990 — Fri, 17 Apr 2020 12:18:37 GMT

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