Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
6 years ago

Translate 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)*...
Need Help
Motasem_Yakhola's avatar
Motasem_Yakhola
Icon for Helper I rankHelper I
6 years ago

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's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
6 years ago

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