Potential bug with date() ?

[zahlenschubser]

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):

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 2, DATE(YR , 2 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH >= 12, DATE(YR + 1, 2 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 5, DATE(YR , 5 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 8, DATE(YR , 8 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 11, DATE(YR , 11 , 1)

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

I double and triple checked all the DATE() functions but I cannot find any errors.

Any ideas?

Full code:

REG_nextbill = VAR YR = YEAR(today() - 5)

               VAR MTH = MONTH(today() - 5)

               RETURN

        switch(TRUE()

            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH <= 3, DATE(YR , 3 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH <= 6, DATE(YR , 6 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH <= 9, DATE(YR , 9 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH <= 12, DATE(YR , 12 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 2, DATE(YR , 2 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH >= 12, DATE(YR + 1, 2 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 5, DATE(YR , 5 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 8, DATE(YR , 8 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH <= 11, DATE(YR , 11 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH = 1, DATE(YR , 1 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH <= 4, DATE(YR , 4 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH >= 11, DATE(YR + 1, 1 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH <= 7, DATE(YR , 7 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH <= 10, DATE(YR , 10 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH <= 5, DATE(yr , 5 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH > 5, DATE(yr + 1 , 5 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH > 6, DATE(yr , 12 , 1)

            , rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH <= 6, DATE(yr , 6 , 1)

            , DATE(YR, MTH , 1)

            )

[zahlenschubser]

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() - 5

                VAR YR = YEAR(TD)

                VAR MTH = MONTH(TD)

                RETURN

        switch(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)

            )

[MFelix]

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.

[zahlenschubser]

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.

[MFelix]

Hi @zahlenschubser 

When I opened the file there were a couple of errors because of dots instead of commas on the formula not sure if this is happening also to you but please see then image below:

After replacing this by commas everything worked properly even after saving, opening and reopening.

[zahlenschubser]

What confuses me in your screenshots is that PBI seems to have replaced only a part of the separators with dots, not all of them. If that's what goes on in the background on saving it's no wonder it bugs out.

[MFelix]

Hi @zahlenschubser ,

I have to be honest in the past (2017/2018) this would happen when we had a computer with regional settings and got a file with different regional settings and then DAX editor would become "confused". We even got problems when copying formulas to the forum because some people got the decimal separator has comma or has dot.

Since they added the functionality for the regional settings and the DAX defnitions I never seen this again. I know this will sound stupid but can you try and replace the dots by comma but be sure to add a space after the comma:

if you have 6.7 you should go to 6, 7

See if that picks up the correct values or goes back to giving you errors.

[zahlenschubser · Accepted solution]

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.

[MFelix]

Have to be honest, PBI Desktop always messes up with the regional settings not sure why but it does, since back way in 2015 when it all started.

If that fixed your issue please remember to accept the solution so it can help others.

[zahlenschubser]

It's not really a solution if I have to undo parts of the program setup to fix an underlying coding issue, but for the sake of getting it resolved on the user side I'll mark it anyway.

[MFelix]

Has I refered the DAX syntax and the regional settings always have been struggling together not sure why but they have.

I always keep the default for DAX syntax that way won't get into any problems.

[zahlenschubser]

That's because command separators are set to comma in the regional settings.

I changed something in the formula and back and let it calculate and got a result

But as soon as I save it bugs out again

It doesn't even show me any errors in the code / code window, just the cryptic message on the bottom.

[MFelix]

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.

[zahlenschubser]

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)

               RETURN

        switch(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)

            )

[MFelix]

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.

[zahlenschubser]

I have a bunch of customer groups that have different billing cycles. Depending on the cycle I want to get the current next billing period for each group.

1) Every three months, but they start in Jan / Feb / Mar and then +3 months

2) Monthly

3) Half Yearly

4) Yearly

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.