topic Python to Dax help in DAX Commands and Tips

Python to Dax help

Anonymous — Tue, 19 Jul 2022 23:30:33 GMT

Hello all,

I am currently trying to convert this below code into something that I can paste into DAX and create a new column that reads a date column and returns the fiscal week. To clarify, my Fiscal Year starts the week containing May 1st (Sun -Sat) and operates on 445 calendaring. If anyone could guide me on how to convert it that would be a huge help, thanks.

Thanks a ton

Re: Python to Dax help

vapid128 — Tue, 19 Jul 2022 23:53:25 GMT

You need a colnum of dates.

let us name it to [date].

add 3 colnums:

MMD = MONTH([Date])&"/"&DAY([Date])

weeknum = WEEKNUM([Date])

IsFiscalWeek = "5/1" in CALCULATETABLE(VALUES(Dates2[MMD]),ALLEXCEPT(Dates2,Dates2[weekIndex]))

Re: Python to Dax help

Anonymous — Wed, 20 Jul 2022 18:06:02 GMT

Hi vapid128

Where does "Dates2[weekIndex] come from? Is that just the original date column?

Re: Python to Dax help

vapid128 — Wed, 20 Jul 2022 18:26:14 GMT

Sorry, my mistake.

IsFiscalWeek = "5/1" in CALCULATETABLE(VALUES(Dates2[MMD]),ALLEXCEPT(Dates2,Dates2[weeknum])

Re: Python to Dax help

Anonymous — Wed, 20 Jul 2022 20:41:39 GMT

vapid128

When I try to make the IsFiscalWeek column I receive the following error message. I've tried changing Date and MMD value formats but none are working.

Thanks

Re: Python to Dax help

vapid128 — Wed, 20 Jul 2022 23:52:01 GMT

IsFiscalWeek = "5/1" in

CALCULATETABLE(

VALUES(Dates2[MMD]),

ALLEXCEPT(Dates2,Dates2[weeknum])

)

And you date colnum should not skip in date

Re: Python to Dax help

Anonymous — Thu, 21 Jul 2022 17:45:32 GMT

I've made the changes and it just returns false. I've tried every different formatting option but it still returns False each time

Re: Python to Dax help

vapid128 — Thu, 21 Jul 2022 22:21:57 GMT

We need all date in date colnum.

If we cant have all date, we can create a date table with all date.

datetable = CALENDAR(MIN(table[date]),MAX(table[date]))