Forum Discussion

zahlenschubser's avatar
zahlenschubser
Helper IV
2 years ago
Solved

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, mos...
Need Help
  • zahlenschubser's avatar
    zahlenschubser
    2 years ago

    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.

     

zahlenschubser's avatar
zahlenschubser
Helper IV
2 years ago

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's avatar
    MFelix
    Super User
    2 years ago

    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's avatar
      zahlenschubser
      Helper IV
      2 years ago

      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's avatar
        MFelix
        Super User
        2 years ago

        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.