Forum Discussion
Potential bug with date() ?
I have a very curious issue with a switch function containing a lot of date() formulas.
What I am trying to do is get a column that shows me when one of our invoicing groups will be billed next, most of them every three months.
So group 44 for example will be due Feb / May / Aug / Nov. Including the year switchover that lead me to the following code within the switch function (full code at the very end):
It worked fine until just recently, when I had to switch group 6 from June to May (once a year), and now I am getting a really weird error in Power BI:
The syntax for 'DATE' is incorrect. (DAX(VAR YR = YEAR(today() - 5) ....
Any ideas?
Full code:
I have the weirdest fix for this ... switch back the regional settings in PBI to the default.
A little annoying, but trying to figure out and fix what PBI messes up in the background is way worse.
15 Replies
Hi zahlenschubser ,
Be carefull that the SWITCH function is done based on the order of the several rows so if the first one is getting a true value the statetment stops, in this case not sure what is group 6 you refer but please check the order of your switch statement because it can be based on that.
The conditions being triggered one after another is fine, as I tried to make them specific for each customer group.
What I intended to do is have switch() evaluate a combination of the customer group code and the current month and then determine when the group will next be up for billing.
For example
, rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1)
supposedly means that if the group is 43 or 45 or 48 AND the current month is Jan/Feb/Mar > output March of the current year as next billing.I tried to streamline and reorder the code a bit so I don't have multiple lines trigger the same date, but I'm still getting the same error. =(
REG_nextbill = VAR YR = YEAR(today() - 5)VAR MTH = MONTH(today() - 5)RETURNswitch(TRUE(), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {4,5,6}|| rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {1,2,3,4,5,6}, DATE(YR , 6 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {7,8,9}, DATE(YR , 9 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {10,11,12}|| rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {7,8,9,10,11,12}, DATE(YR , 12 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {1,2}, DATE(YR , 2 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {3,4,5}, DATE(YR , 5 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {6,7,8}, DATE(YR , 8 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {9,10,11}, DATE(YR , 11 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH = 12, DATE(YR + 1, 2 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH = 1, DATE(YR , 1 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {2,3,4}, DATE(YR , 4 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {5,6,7}, DATE(YR , 7 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {8,9,10}, DATE(YR , 10 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {11,12}, DATE(YR + 1, 1 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {6,7,8,9,10,11,12}, DATE(YR + 1 , 5 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {1,2,3,4,5}, DATE(YR , 5 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 49 , DATE(YR , MTH , 1), DATE(1999, MTH , 1))Hi zahlenschubser ,
What is the result you want to achieve? Can you give an example?
Again believe that the problem is the order of the options that will return the incorrect order for the result you want to achieve.
Tried to streamline it a bit more, but same problem.
The weird part is that right after the calculation finishes, it does show me the correct periods as a result, but as soon as I save the file it reverts to the previous weird DATE error state.REG_nextbill = VAR TD = TODAY() - 5VAR YR = YEAR(TD)VAR MTH = MONTH(TD)RETURNswitch(TRUE(), rechnungsgruppe[rechnungsgruppe_pk] = 49 , DATE(YR , MTH , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH = 1, DATE(YR , 1 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {1,2}, DATE(YR , 2 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {2,3,4}, DATE(YR , 4 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {3,4,5}|| rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {1,2,3,4,5}, DATE(YR , 5 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {4,5,6}|| rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {1,2,3,4,5,6}, DATE(YR , 6 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {5,6,7}, DATE(YR , 7 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {6,7,8}, DATE(YR , 8 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {7,8,9}, DATE(YR , 9 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {8,9,10}, DATE(YR , 10 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {9,10,11}, DATE(YR , 11 , 1), rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {10,11,12}|| rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {7,8,9,10,11,12}, DATE(YR , 12 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {11,12}, DATE(YR + 1, 1 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH = 12, DATE(YR + 1, 2 , 1), rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {6,7,8,9,10,11,12}, DATE(YR + 1 , 5 , 1), DATE(1999, MTH , 1))Hi zahlenschubser ,
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Can you please try this one? https://we.tl/t-D7938k4lHn
Try to edit the code in one of the calculated columns, then it should show a date result, but as soon as you save it flips back to the DATE() syntax error.
